Pietro said:
A fiscal month is a month that always starts on Staurday and ends on Friday.
example of 2008 fiscal months:
January: from 12/29/2007 to 01/25/2008
February: from 01/26/2008 to 02/22/2008
March: from 02/23/2008 to 03/28/2008
April: from 03/29/2008 to 04/25/2008
etc.
This is what i mean by fiscal month...
I agree with Douglas. I can't tell from your example what your rule for
fiscal month is but I'll take a guess. It looks like the fiscal month
perhaps ends on the final Friday of the month and begins the day after
the final Friday of the previous month. If so, try:
'---Begin module code---
Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year(dtD), Month(dtD) + 1,
(-WeekDay(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod 7)
End Function
Public Function FiscalMonthAndYear(dtD As Date) As String
If dtD > LastXDay(dtD, 6) Then
'After final Friday in month
FiscalMonthAndYear = Format(DateAdd("m", 1, dtD), "mmmm") & " " &
Year(DateAdd("m", 1, dtD))
Else
FiscalMonthAndYear = Format(dtD, "mmmm") & " " & Year(dtD)
End If
End Function
'----End module code----
Example:
FiscalMonthAndYear(#12/28/07#) => December 2007
FiscalMonthAndYear(#12/29/07#) => January 2008
FiscalMonthAndYear(#1/25/08#) => January 2008
FiscalMonthAndYear(#1/26/08#) => February 2008
FiscalMonthAndYear(#2/22/08#) => February 2008
FiscalMonthAndYear(#2/23/08#) => February 2008
FiscalMonthAndYear(#2/29/08#) => February 2008
FiscalMonthAndYear(#3/1/08#) => March 2008
FiscalMonthAndYear(#3/28/08#) => March 2008
FiscalMonthAndYear(#3/29/08#) => April 2008
FiscalMonthAndYear(#4/25/08#) => April 2008
FiscalMonthAndYear(#4/26/08#) => May 2008
Note that this would mean that the fiscal month for February 2008 ends
on Friday the 29th instead of on the 22nd as you show in your example.
The LastXDay function goes to the date of the first day of the following
month then subtracts the appropriate number of days necessary to get to
the final XDay (Friday in this case) of the month by determining on
which weekday the first day of the next month falls. The sample code
above probably contains extraneous carriage returns due to line wrap
that need to be removed.
James A. Fortune
(e-mail address removed)
