Rdichpally
asked on
Date format for fiscal calender
I have a code in VBA (Excel) where I am taking the fiscal month into a excel report with the code below. The problem is with the fiscal calendat (attached) where for 02/26/2011 the fiscal month should be March and not february as it is showing as per the code below. Any help to correct the fiscal month on the excel report code below will be appreciated.
Private Sub Headers()
If SheetName = "Maturity" Then
Range("O4").Value = Format(ReportDate, "MMMM")[
Range("R4").Value = ReportDate
Else
Range("M4").Value = Format(ReportDate, "MMMM")
Range("P4").Value = ReportDate
End If
Range("A1").Select
End Sub
Fiscal-Calendar-2011.xls
Private Sub Headers()
If SheetName = "Maturity" Then
Range("O4").Value = Format(ReportDate, "MMMM")[
Range("R4").Value = ReportDate
Else
Range("M4").Value = Format(ReportDate, "MMMM")
Range("P4").Value = ReportDate
End If
Range("A1").Select
End Sub
Fiscal-Calendar-2011.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You'll need the analysis toolpak installed for the weeknum function (Tools \ Add-ins)
Thomas
Thomas
ASKER
This may work for the current week but next week when we are at week ending 03/05/2011, it will give Month as April as we are incrementing the month. Please advise. Thanks.
Nope, if you do
debug.Print getfiscalmonth(datevalue(" 3/5/2011") )
you get March.
It's a question of where your Reportdate comes from, not an issue of my function.
Thomas
debug.Print getfiscalmonth(datevalue("
you get March.
It's a question of where your Reportdate comes from, not an issue of my function.
Thomas
ASKER
how about march last week (03/26/2011) when the fifth week is still in fiscal calnder march but according to your code, it will take the fiscal week as in april instad of march.. please advise.
Here... This code should work properly for all your fiscal weeks.
Public Function FiscalMonth(inDate As Date) As String
Dim wk As Integer
wk = Format(inDate, "ww")
Select Case True
Case Between(wk, 1, 4)
FiscalMonth = "January"
Case Between(wk, 5, 8)
FiscalMonth = "February"
Case Between(wk, 9, 13)
FiscalMonth = "March"
Case Between(wk, 14, 17)
FiscalMonth = "April"
Case Between(wk, 18, 21)
FiscalMonth = "May"
Case Between(wk, 22, 26)
FiscalMonth = "June"
Case Between(wk, 27, 30)
FiscalMonth = "July"
Case Between(wk, 31, 34)
FiscalMonth = "August"
Case Between(wk, 35, 39)
FiscalMonth = "September"
Case Between(wk, 40, 43)
FiscalMonth = "October"
Case Between(wk, 44, 47)
FiscalMonth = "November"
Case Between(wk, 48, 52)
FiscalMonth = "December"
End Select
End Function
Public Function Between(i1, i2, i3) As Boolean
If i1 >= i2 And i1 <= i3 Then
Between = True
Else
Between = False
End If
End Function
ASKER
This code is not working. Are there any changes/ modifications that need to be done?
ASKER
How are you getting the week part (ww) in the code? This is what is causing issues.
Week is being generated by the Format function. If that doesn't work, your fiscal weeks are not close enough to the calendar weeks...
Replace with the code below. Sorry... But you'll have to add the rest of the dates yourself... I've put in Jan to March as examples.
Public Function FiscalMonth(inDate As Date) As String
Select Case True
Case Between(inDate, "2010-12-26", "2011-01-22")
FiscalMonth = "January"
Case Between(inDate, "2011-01-23", "2011-02-19")
FiscalMonth = "February"
Case Between(inDate, "2011-02-20", "2011-03-26")
FiscalMonth = "March"
Case Between(inDate, 14, 17)
FiscalMonth = "April"
Case Between(inDate, 18, 21)
FiscalMonth = "May"
Case Between(inDate, 22, 26)
FiscalMonth = "June"
Case Between(inDate, 27, 30)
FiscalMonth = "July"
Case Between(inDate, 31, 34)
FiscalMonth = "August"
Case Between(inDate, 35, 39)
FiscalMonth = "September"
Case Between(inDate, 40, 43)
FiscalMonth = "October"
Case Between(inDate, 44, 47)
FiscalMonth = "November"
Case Between(inDate, 48, 52)
FiscalMonth = "December"
End Select
End Function
Public Function Between(i1, i2 As Date, i3 As Date) As Boolean
If i1 >= i2 And i1 <= i3 Then
Between = True
Else
Between = False
End If
End Function
Replace with the code below. Sorry... But you'll have to add the rest of the dates yourself... I've put in Jan to March as examples.
Public Function FiscalMonth(inDate As Date) As String
Select Case True
Case Between(inDate, "2010-12-26", "2011-01-22")
FiscalMonth = "January"
Case Between(inDate, "2011-01-23", "2011-02-19")
FiscalMonth = "February"
Case Between(inDate, "2011-02-20", "2011-03-26")
FiscalMonth = "March"
Case Between(inDate, 14, 17)
FiscalMonth = "April"
Case Between(inDate, 18, 21)
FiscalMonth = "May"
Case Between(inDate, 22, 26)
FiscalMonth = "June"
Case Between(inDate, 27, 30)
FiscalMonth = "July"
Case Between(inDate, 31, 34)
FiscalMonth = "August"
Case Between(inDate, 35, 39)
FiscalMonth = "September"
Case Between(inDate, 40, 43)
FiscalMonth = "October"
Case Between(inDate, 44, 47)
FiscalMonth = "November"
Case Between(inDate, 48, 52)
FiscalMonth = "December"
End Select
End Function
Public Function Between(i1, i2 As Date, i3 As Date) As Boolean
If i1 >= i2 And i1 <= i3 Then
Between = True
Else
Between = False
End If
End Function
I hadn't picked that third month thing. Here is my update:
Function getFiscalMonth(dtDate As Date) As String
Dim lgWeek As Long, strMonth As String
lgWeek = Application.WorksheetFunction.WeekNum(dtDate)
strMonth = 1 + Int((lgWeek - 1-int(lgweek/13)-int(lgweek/13)) / 4)
getFiscalMonth = Format(DateValue(strMonth & "/1/2011"), "mmmm")
End Function
ASKER
The above code is not working when I run the report for the date 04/30/2011. The Fiscal month should show May but the fiscal month is showing April.
Sorry, typo in my function, there should be one /13 and one /53
Function getFiscalMonth(dtDate As Date) As String
Dim lgWeek As Long, strMonth As String
lgWeek = Application.WorksheetFunction.WeekNum(dtDate)
strMonth = 1 + Int((lgWeek - 1 - Int(lgWeek / 13) - Int(lgWeek / 53)) / 4)
getFiscalMonth = Format(DateValue(strMonth & "/1/2011"), "mmmm")
End Function
Would it not be easier to have a lookup on a table of dates? Setting the lookup as TRUE (ie not exact match) will enable all dates to be looked up. Or you could use the CEING function to round up a date to the end of the week. The lookup table could be extensive if you need to lookup dates from different years.
ASKER
Do you think we can lookup the table calendarweek (attached with data and structure) and then code for the fiscal month. Please let me know if you can change the code by using the attached table. Many Thanks.
--Raj
dbo-CalendarWeek.csv
table-calendarweek.txt
--Raj
dbo-CalendarWeek.csv
table-calendarweek.txt
ASKER
Also, how can I change the code to reference the above fiscal month if I need the month and the year; This code is in my Excel VBA :
Range("I2").Value = "% OF SALES, " & UCase(TODAYDATE)
Range("I2").Value = "% OF SALES, " & UCase(TODAYDATE)
ASKER
Thanks.
You're sure not to get an object error if you use the format function to get the week number.
Open in new window
Much more concise than the road I was going down though...
Jeff