Solved

Date format for fiscal calender

Posted on 2011-03-03
17
658 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Rdichpally
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35029708
Use the attached function to get your fiscal month

Function getFiscalMonth(dtDate As Date) As String
Dim lgWeek As Long, strMonth As String

lgWeek = Application.WorksheetFunction.WeekNum(dtDate)

strMonth = 1 + Int((lgWeek - 1) / 4)

getFiscalMonth = Format(DateValue(strMonth & "/1/2011"), "mmmm")

End Function

Open in new window


as in

Private Sub Headers()
       'I assume you've defined reportdate at the module level
       If SheetName = "Maturity" Then
       
        Range("O4").Value = getfiscalmonth(ReportDate)  
       Range("R4").Value = ReportDate
       Else
        Range("M4").Value = getfiscalmonth(ReportDate)
        Range("P4").Value = ReportDate
       End If
       Range("A1").Select
End Sub

Open in new window


Thomas
0
 
LVL 1

Expert Comment

by:jeff77tor
ID: 35029875
Since I get an object error with the above code, you could also try adding the following code to get the month...

You're sure not to get an object error if you use the format function to get the week number.

Function getFiscalMonth(dtDate As Date) As String
Dim lgWeek As Long, strMonth As String

lgWeek = Format(dtDate, "ww")

strMonth = 1 + Int((lgWeek - 1) / 4)

getFiscalMonth = Format(DateValue(strMonth & "/1/2011"), "mmmm")

End Function

Open in new window



Much more concise than the road I was going down though...

Jeff

0
 
LVL 39

Expert Comment

by:nutsch
ID: 35029923
You'll need the analysis toolpak installed for the weeknum function (Tools \ Add-ins)

Thomas
0
 

Author Comment

by:Rdichpally
ID: 35030024
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.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35030098
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
0
 

Author Comment

by:Rdichpally
ID: 35030423
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.
0
 
LVL 1

Expert Comment

by:jeff77tor
ID: 35030636
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

Open in new window

0
 

Author Comment

by:Rdichpally
ID: 35030889
This code is not working. Are there any changes/ modifications that need to be done?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Rdichpally
ID: 35031118
How are you getting the week part (ww) in the code? This is what is causing issues.
0
 
LVL 1

Expert Comment

by:jeff77tor
ID: 35031410
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
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35031415
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

Open in new window

0
 

Author Comment

by:Rdichpally
ID: 35039964
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.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35040037
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

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 35043462
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.
0
 

Author Comment

by:Rdichpally
ID: 35057997
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
0
 

Author Comment

by:Rdichpally
ID: 35061853
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)
0
 

Author Closing Comment

by:Rdichpally
ID: 35158708
Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Consolidate xl 2010 worksheets with text 2 24
Excel Conditional Formatting in a Macro 4 26
Excel - find text within text? 1 24
the code is not looping through 11 34
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now