Fiscal month Function in VBA

[LearnVBA83]

Hi all,

I'm so excited to have joined Mr. Excel and this will be my first post! I'm very basic with VBA but want to learn so much more. I think I missed my calling not becoming a computer programmer, but accounting it is for now! In my company we have the weirdest month end calendar and I have a file saved on a local drive with all dates from 2015 to 2099 listed. Beside each date is the Fiscal Month (Oct-Sept) in a column Fiscal Period (1-12) in a column, Fiscal QTR (Q1 - Q4) in a column, and Fiscal Year. The file like I said is saved on the local drive and is probably 31,000 lines. Would it be possible to create a custom function using vba that would look up a date in our financial models and return the value from the list I have saved on my local drive? For instance in excel I would type =FYmonth(A1) with A1 being a date and it would lookup that date on the file saved on the local drive and return the month name. It would also be great for the macro to then copy and paste as values so i'm not having hidden links between files. I'm sure there are so many more efficient ways to do this but I wanted to start the conversation with this method I thought of.

Thanks,
breid

 

[Tetra201]

Unless your company's month end calendar is completely random (which is hard to believe),
there is a chance all you need could be accomplished without a lookup file.
Can you post an example?

 

[LearnVBA83]

Unless your company's month end calendar is completely random (which is hard to believe),
there is a chance all you need could be accomplished without a lookup file.
Can you post an example?

Wow I never really thought of it that way. I guess because someone created a file will all the dates out to 2099. Ok so i'm looking for trends:

FY15
1. All months begin on a Sunday and End on a Saturday
2. Our total year is Oct - Sept with Oct being period 1 and September period 12
3. Looks like Nov (period 2), March (period 6), June (period 9), and Sept (period 12) all have 5 weeks (35 days) except for ever so often we have a 53 week year in which period 12 has 6 weeks (42 days). I can't find a solid trend there for instance FY15 period 12 was 6 weeks. The next period 12 6 week year doesn't occur until 2020 and then 2026 after that.
4. FY15 was 10/24/14 to 10/3/15.

So maybe someone adds 28 + 35 + 28 + 28 + 28 + 35 + 28 + 28 + 35 + 28 + 28 + 35 and every several years the last 35 is replaced with 42 days.

It's a weird Fiscal period that's for sure!

Thanks for the help!

 

[Tetra201]

... 1. All months begin on a Sunday and End on a Saturday
... 4. FY15 was 10/24/14 to 10/3/15

10/24/14 was Friday -- is the date correct?
FY15 had only 345 days -- are the dates correct?

 

[LearnVBA83]

I'm so sorry Tetra! Glanced at the print out on my cube wall and made a typo.

FY15 Total = 9/28/14 to 10/3/15

9/28/14 - 10/25/14
10/26/14 - 11/29/14 (5 Weeks)
11/30/14 - 12/27/14
12/28/14 - 1/24/15
1/25/15 - 2/21/15
2/22/15 - 3/28/15 (5 Weeks)
3/29/15 - 4/25/15
4/26/15 - 5/23/15
5/24/15 - 6/27/15 (5 Weeks)
6/28/15 - 7/25/15
7/26/15 - 8/22/15
8/23/15 - 10/03/15 (Random 6 week)

FY16 Total = 10/04/15 to 10/01/16
28
35
28
28
28
35
28
28
35
28
28
35

 

[LearnVBA83]

10/24/14 was Friday -- is the date correct?
FY15 had only 345 days -- are the dates correct?

I went through my spreadsheet and it looks like 53 weeks are:
2015
2020
2026
2032
2037
2043
2048
2054
2060
2065
2071
2076
2082
2088
2093
2099

I subtracted each month from the prior starting with 2020-2015 and the trend seems to be 5,6,6,5,6 5,6,6,5,6

 

[Tetra201]

Give a try to this user-defined function.
Here is a brief "how-to" on UDF's.

Function FYINFO(ByVal d As Variant, t As String) As Variant
' Code by Tetra201 2016-12-01
'
' Usage: =FYINFO(date,type)
' type  "m" returns name of fiscal month ("October" through "September")
'       "p" returns fiscal period (1 through 12)
'       "q" returns fiscal quarter ("Q1" through "Q4")
'       "w" returns fiscal week (1 through 53)
'       "y" returns fiscal year
'
    Dim DateArr(1 To 13) As Long
    DateInc = Array(28, 35, 28, 28, 28, 35, 28, 28, 35, 28, 28)
    FYINFO = DateValue(d)
    If IsError(FYINFO) Then Exit Function
    DateInp = FYINFO
    Select Case Month(DateInp)
        Case 1 To 8: FY = Year(DateInp)
        Case 11, 12: FY = Year(DateInp) + 1
        Case Else: FY = Year(DateInp) + Month(DateInp) - 9
            i = DateSerial(FY - 1, 10, 1)
            DateArr(1) = i - WorksheetFunction.Weekday(i, 14) + 4
            i = DateSerial(FY, 10, 1)
            DateArr(13) = i - WorksheetFunction.Weekday(i, 14) + 4
            If DateInp < DateArr(1) Then FY = FY - 1
            If DateInp >= DateArr(13) Then FY = FY + 1
    End Select
    i = DateSerial(FY - 1, 10, 1)
    DateArr(1) = i - WorksheetFunction.Weekday(i, 14) + 4
    i = DateSerial(FY, 10, 1)
    DateArr(13) = i - WorksheetFunction.Weekday(i, 14) + 4
    For i = 2 To 12 Step 1
        DateArr(i) = DateArr(i - 1) + DateInc(i - 2)
    Next i
    For FP = 1 To 12 Step 1
        If DateInp < DateArr(FP + 1) Then Exit For
    Next FP
    Select Case UCase(t)
        Case "M": FYINFO = Format(DateSerial(FY, FP + 9 - 12 * Round((FP + 9) / 25, 0), 1), "mmmm")
        Case "P": FYINFO = FP
        Case "Q": FYINFO = "Q" & WorksheetFunction.RoundUp(FP / 3, 0)
        Case "W": FYINFO = (DateInp - DateArr(1)) \ 7 + 1
        Case "Y": FYINFO = FY
        Case Else: FYINFO = DateValue("")
    End Select
End Function

 

[LearnVBA83]

Give a try to this user-defined function.
Here is a brief "how-to" on UDF's.

HiTetra,

Thanks for putting together the above code. When running the function i'm getting the error "Compile error: Wrong number of arguments or invalid property assignment" It's also highlighting the word Round in the code. Any idea what this error could be? I opened an excel document and typed 12/2/16 in cell A1. In B1 I typed =FYINFO(A1,M). Is there a way to step through the function (F8) like I can do with a normal macro so that I can try to point out the error?

Thanks Again.

 

[Tetra201]

... When running the function i'm getting the error "Compile error: Wrong number of arguments or invalid property assignment" It's also highlighting the word Round in the code. Any idea what this error could be? I opened an excel document and typed 12/2/16 in cell A1. In B1 I typed =FYINFO(A1,M). Is there a way to step through the function (F8) like I can do with a normal macro so that I can try to point out the error?

- The code has been tested to work on Excel 2010-32 and 2013-32.
- Copy the code as is and place it in a VB module like a regular macro.
- Use the following syntax: =FYINFO(A1,"m").

 

[LearnVBA83]

- The code has been tested to work on Excel 2010-32 and 2013-32.
- Copy the code as is and place it in a VB module like a regular macro.
- Use the following syntax: =FYINFO(A1,"m").

Wow Tetra! This is amazing! It's unbelievable at what VBA can do. You have truly blown my mind. So when I open a blank workbook and paste the code in a module it seems to be working fine. I will do extensive testing over the weekend. However, If I try to paste the code in a module in a custom add-in that i've created the function gives me the error. Any reason why that would be? I've created an add-in and i'm using a custom UI editor to create a custom ribbon. I'd love to have this function as part of the add-in. Also, when you pasted the code above, is there something you clicked to post the code in that nice format with the scroll bars on the sides?

Thanks again. What you have created is unbelievable to me!