Add Fiscal Quarter to Fiscal Macro

Hello!

I have a Fiscal Date macro that will looks at a date to give me the current fiscal month or week, but I would also like to add the functionality for it to give me the quarter.

In my business, the fiscal year starts in October. The quarters of course are as follows:

Oct-Dec = Q1
Jan-Mar = Q2
Apr-Jun = Q3
Jul-Sep = Q4

I would like for the quarter to be formatted with the year ex. FY14Q1

Please let me know what additional information is needed.

Here is the Code, but I have also attached an Excel file with the .bas file.

Please Login or Register  to view this content.

Hi,

Try this:

Public Function FiscalDate(fdate As Date, rType As String) As String

Dim FMList(1 To 12, 1 To 2) As Variant

Application.Volatile False 'makes function non-volatile - function gets recalculated only when the input variables change

FMList(1, 1) = "P01"
FMList(2, 1) = "P02"
FMList(3, 1) = "P03"
FMList(4, 1) = "P04"
FMList(5, 1) = "P05"
FMList(6, 1) = "P06"
FMList(7, 1) = "P07"
FMList(8, 1) = "P08"
FMList(9, 1) = "P09"
FMList(10, 1) = "P10"
FMList(11, 1) = "P11"
FMList(12, 1) = "P12"

FMList(1, 2) = 5
FMList(2, 2) = 4

FMList(4, 2) = 5
FMList(5, 2) = 4
FMList(6, 2) = 4
FMList(7, 2) = 5
FMList(8, 2) = 4
FMList(9, 2) = 4
FMList(10, 2) = 5
FMList(11, 2) = 4
FMList(12, 2) = 4

Dim fystart As Date, this_year As Date, last_year As Date
Dim wCount As Integer, i As Long
Dim fyear As String, fquar As String

this_year = DateSerial(Year(fdate), 9, 31 - Weekday(DateSerial(Year(fdate), 9, 30), vbSunday))
last_year = DateSerial(Year(fdate) - 1, 9, 31 - Weekday(DateSerial(Year(fdate) - 1, 9, 30), vbSunday))

'set fiscal year start date
Select Case Month(fdate)
Case Is > 9: fystart = this_year

Case Is < 9: fystart = last_year

Case Is = 9: fystart = IIf(fdate >= this_year, this_year, last_year)
End Select

Select Case Month(fdate)
Case 10 To 12: fquar = "Q1"
Case 7 To 9: fquar = "Q4"
Case 4 To 6: fquar = "Q3"
Case 1 To 3: fquar = "Q2"
End Select
'get fiscal year quarter

'leap-year check
FMList(3, 2) = IIf(DateDiff("d", DateSerial(Year(fystart) + 1, 1, 1), DateSerial(Year(fystart) + 1, 12, 31)) = 364, 4, 5)

'current fiscal year for the specified date string
fyear = "Y" & Right(Year(fystart) + 1, 2)

wCount = DateDiff("w", fystart, fdate) + 1

For i = 1 To 12
wCount = wCount - FMList(i, 2)
If (wCount <= 0) Then
If (rType = "m") Then
FiscalDate = FMList(i, 1) & fyear
End If
If (rType = "w") Then
FiscalDate = FMList(i, 1) & "W" & (wCount + FMList(i, 2)) & fyear
End If
If (rType = "q") Then
FiscalDate = "F" & fyear & fquar
End If
Exit For
End If
Next i

End Function

You're welcome and thanks for the rep!