End of Quarter Based on Fiscal Year

[Michael]

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

 

[isabelle]

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 :

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:d7d9079f-6000-4dab-9b12-496e2fd743a1@n10g2000vbg.googlegroups.com...