Excel 2000 - VBA and fiscal periods

  • Thread starter Thread starter Matt.
  • Start date Start date
M

Matt.

Hi all!

I've written a small application that grabs data from 6 different data
sources, and puts them all into Excel for graphing. I was asked to do this
by Month. So I did. Calendar month. It now turns out that they meant
Fiscal Months. (i.e. October 2003 starts 2003/09/29 and ends 2003/10/26).

Is there anything out there allowing me define fiscal periods and then
compare a date against the definition?

cheers,
Matt.
 
Retail months follow the same pattern with a fluctuation only once ever many
years (maybe 11).

Every third retail month has five weeks, the rest have four. The last day
of the current retail month is Oct 4 and this is a five week month.

With the rules it shouldn't be to hard to code.

Jesse Hamilton
 
Hi all!

In answer to my query, I created this. It's not really pretty, but I think
it will serve my needs. Feel free to use it if you think it will help you
too.

Pass a date to sub FiscalStartEnd, and it should pass back the start and end
dates of the fiscal period that date is in. The WeekNumber function can be
found on Microsoft's web site. Search the knowledgebase for an error in the
datepart or format function.

cheers,
Matt.

Function FiscalMonth(datDate As Date) As Integer

Select Case WeekNumber(datDate)
Case 1 To 4
FiscalMonth = 1
Case 5 To 8
FiscalMonth = 2
Case 9 To 13
FiscalMonth = 3
Case 14 To 17
FiscalMonth = 4
Case 18 To 21
FiscalMonth = 5
Case 22 To 26
FiscalMonth = 6
Case 27 To 30
FiscalMonth = 7
Case 31 To 35
FiscalMonth = 8
Case 36 To 39
FiscalMonth = 9
Case 40 To 43
FiscalMonth = 10
Case 44 To 47
FiscalMonth = 11
Case 48 To 52
FiscalMonth = 12
Else
FiscalMonth = 0
End Select

End Function

Sub FiscalStartEnd(datDate As Date, Optional datStart As Date, Optional
datEnd As Date)
Dim intYear As Integer
intYear = Year(datDate)
Select Case FiscalMonth(datDate)
Case 1
Select Case intYear
Case 2003
datStart = #1/1/2003#
datEnd = #1/26/2003#
Case 2004
End Select
Case 2
Select Case intYear
Case 2003
datStart = #1/27/2003#
datEnd = #2/23/2003#
Case 2004
End Select
Case 3
Select Case intYear
Case 2003
datStart = #2/24/2003#
datEnd = #3/30/2003#
Case 2004
End Select
Case 4
Select Case intYear
Case 2003
datStart = #3/31/2003#
datEnd = #4/27/2003#
Case 2004
End Select
Case 5
Select Case intYear
Case 2003
datStart = #4/28/2003#
datEnd = #5/25/2003#
Case 2004
End Select
Case 6
Select Case intYear
Case 2003
datStart = #5/26/2003#
datEnd = #6/29/2003#
Case 2004
End Select
Case 7
Select Case intYear
Case 2003
datStart = #6/30/2003#
datEnd = #7/27/2003#
Case 2004
End Select
Case 8
Select Case intYear
Case 2003
datStart = #7/28/2003#
datEnd = #8/24/2003#
Case 2004
End Select
Case 9
Select Case intYear
Case 2003
datStart = #8/25/2003#
datEnd = #9/28/2003#
Case 2004
End Select
Case 10
Select Case intYear
Case 2003
datStart = #9/29/2003#
datEnd = #10/26/2003#
Case 2004
End Select
Case 11
Select Case intYear
Case 2003
datStart = #10/27/2003#
datEnd = #11/23/2003#
Case 2004
End Select
Case 12
Select Case intYear
Case 2003
datStart = #11/24/2003#
datEnd = #12/31/2003#
Case 2004
End Select
End Select

End Sub
 
Back
Top