I am trying to come up with a fuction that takes a date and depending
on the month that the fiscal year ends return the last day of the
quarter. For example date 12/21/2011 on a fiscal year that ends Oct 31
would return 1/31/2012.

Basicly I am trying to round dates up to the date at the end of the
fiscal quarter it falls in.

any help would be appreciated

hi Michael,

Sub Macro1()
Dim n As Date, y As Integer, endY As Date, startY As Date
Dim t As Date, t1 As Date, t2 As Date, t3 As Date

n = DateSerial(2011, 12, 21)

If Month(n) <= 10 And Day(n) <= 31 Then y = Year(n) Else y = Year(n) + 1
endY = DateSerial(y, 10, 31)
startY = DateSerial(Year(endY) - 1, Month(endY), Day(endY) + 1)
t1 = DateSerial(Year(startY), Month(startY) + 3, Day(startY) - 1)
t2 = DateSerial(Year(startY), Month(startY) + 6, Day(startY) - 1)
t3 = DateSerial(Year(startY), Month(startY) + 9, Day(startY) - 1)

Select Case n
Case Is startY: t = t1
Case Is t1: t = t2
Case Is t2: t = t3
Case Is t2: t = endY
End Select

Debug.Print t
End Sub


--
isabelle



Le 2011-12-21 12:37, Michael a écrit :
I am trying to come up with a fuction that takes a date and depending
on the month that the fiscal year ends return the last day of the
quarter. For example date 12/21/2011 on a fiscal year that ends Oct 31
would return 1/31/2012.

Basicly I am trying to round dates up to the date at the end of the
fiscal quarter it falls in.

any help would be appreciated

I've got...
Fiscal Year End in B2
Report Date in C2

In B3:B7 is...
=EOMONTH(B2,3)
=B2
=EOMONTH(B2,-3)
=EOMONTH(B2,-6)
=EOMONTH(B2,-9)

In D2 is the last day of the quarter...
=INDEX(B3:B7,MATCH(C2,B3:B7,-1),1)

Looks like it works.
---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Data Options Excel add-in... Color, Delete, Insert: rows/dates/random data)



"Michael"
wrote in message
...
I am trying to come up with a fuction that takes a date and depending
on the month that the fiscal year ends return the last day of the
quarter. For example date 12/21/2011 on a fiscal year that ends Oct 31
would return 1/31/2012.

Basicly I am trying to round dates up to the date at the end of the
fiscal quarter it falls in.

any help would be appreciated