Home |
Search |
Today's Posts |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 - VBA and fiscal periods
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 "Matt." wrote in message .. . 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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any tools on Excel to determine the delayed periods? | Excel Discussion (Misc queries) | |||
counting periods Excel | Excel Worksheet Functions | |||
EXCEL LOOKUP FISCAL YEAR WITH DATE | Excel Worksheet Functions | |||
Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
periods of time in excel | Excel Programming |