Given a 4-5-4 calendar and a date, how do I determine what fiscal week that date falls in?

Question

We have a 4-5-4 calendar in which the fiscal year starts on a Sunday in February. For FY 2016, that first day is actually in January -- it's Sunday, January 31st.

I need to write a function that takes a date as an input and returns the fiscal week, such as 201552, which would be the 52nd fiscal week of fiscal year 2015.

I think the first step is figuring out the starting day of the input date's fiscal year. I know that it's always a Sunday, but how do I know whether it's the first Sunday in calendar February, or the last Sunday in calendar January?

(Fortunately for this function's purposes I can ignore the occasional 53rd week; I can just return week 52 in that case. That's because (I'm told) that years having 53 fiscal weeks are not predictable (at this company anyway) and are determined by human whim.)

Any suggestions?

UPDATE:

I've been given a document with FY calendars for our company from FY2005 through FY2017. The pattern I'm seeing is that:

If Feb 1st is a Monday, Tuesday, or Wednesday, then first day of FY is last Sunday of January.
If Feb 1st is a Thursday, Friday, Saturday, or Sunday, then first day of FY is first Sunday of February.

I think that gives me what I need.

I'm using Excel VBA. All I have so far is a function that takes a date as an input, and sets variables for the year, month number, and day of month for that input. It isn't yet a matter of trying code; first I need to figure out what to try. — Greg Lovern
@GregLovern Please edit your Question to clarify and add detail, rather than post as comments. Also, add a programming language as a Tag on the Question. That tag is used by the automatic code-formatting facility in Stack Overflow. — Basil Bourque
@ p._phidot_, Excel's WEEKNUM() worksheet function doesn't know about the 4-5-4 calendar. First, you'd have to supply it with the starting day in the return_type parameter, so you'd have to have another function to determine what that day is, and that is harder than the simple job WEEKNUM does. Then, you'd have to subtract 4 because WEEKNUM thinks the first week in January is week 1. Or at least you'd usually have to subtract 4; I'm not sure offhand if sometimes it would be 3 or 5. and for the end of the year, you'd have to add, what, 48, usually? Using WEEKNUM just makes the job harder. — Greg Lovern

Greg Lovern Greg Lovern · Accepted Answer · 2015-06-16T20:59:39

I think the first step is to find the day of the week for Feb 1st of the input date's year.

Next, find the first day of the FY, which is based on that Feb 1st day of week. If it's a Mon, Tue, or Wed, then first day of FY is last Sunday of January. Otherwise, first day of FY is first Sunday of January.

Next, determine whether the input date is in that FY or the one before.

Then, if the input date is in the previous FY, get the first day of that FY.

Next, count the days from that first day of the FY, to the day of the input date. Divide by 7, rounding UP to the next integer. That is the FY week.

At that point, I'll know the input date's FY year and FY week, and can return it.

UPDATE:

Here's what I have; it works in my tests:

Public Function ConvertDateToRawFYWeek(convert_date As Date) As String
'Fiscal year starts on either:
    'the last Sunday in January (if Feb 1st is a Mon, Tue, or Wed), OR:
    'the first Sunday in February (if Feb 1st is Thur, Fri, Sat, or Sun).

    Dim iCalendarYearOfInputDate As Long, iInputMonth As Long, iInputDay As Long, iTmpYear As Long
    Dim iFebFirstOfTmpYear As Long, strFebFirstWeekdayOfTmpYear As String
    Dim iFirstDayofFYOfTmpYear As Long
    Dim iFiscalYearOfInputDate As Long
    Dim iDayOfInputDate As Long
    Dim iDayOfFY As Long, iWeekOfFY As Long, strWeekOfFY As String
    Dim bDone As Boolean

    iCalendarYearOfInputDate = Year(convert_date)
    iInputMonth = Month(convert_date)
    iInputDay = Day(convert_date)
    iDayOfInputDate = CLng(DateValue(convert_date))


    bDone = False 'init.
    iTmpYear = iCalendarYearOfInputDate 'init.
    Do

            '***get the day of the week of feb 1st of tmp date's year:
            iFebFirstOfTmpYear = DateSerial(iTmpYear, 2, 1)
            strFebFirstWeekdayOfTmpYear = Format(iFebFirstOfTmpYear, "DDDD")

            '***get the first day of the FY of the tmp date's year:
            Select Case strFebFirstWeekdayOfTmpYear
                Case "Monday"
                    'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 31st:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 1
                Case "Tuesday"
                    'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 30th:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 2
                Case "Wednesday"
                    'first day of the tmp year's FY is the last Sunday of January, which for the tmp year is Jan 29th:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear - 3
                Case "Thursday"
                    'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 4th:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 3
                Case "Friday"
                    'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 3rd:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 2
                Case "Saturday"
                    'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 2nd:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear + 1
                Case "Sunday"
                    'first day of the tmp year's FY is the first Sunday of February, which for the tmp year is Feb 1st:
                    iFirstDayofFYOfTmpYear = iFebFirstOfTmpYear
            End Select

            '***get the fiscal year of the input date:
            If iDayOfInputDate >= iFirstDayofFYOfTmpYear Then
                iFiscalYearOfInputDate = iTmpYear
                bDone = True
            Else
                iTmpYear = iTmpYear - 1 'loop again.
            End If

    Loop Until bDone


    '***count the days from that first day of the FY, to the day of the input date.
    'Divide by 7, rounding UP to the next integer. That is the FY week.
    iDayOfFY = iDayOfInputDate - iFirstDayofFYOfTmpYear
    iWeekOfFY = Round((iDayOfFY / 7) + 0.50000000000001) 'round up to next integer.
    strWeekOfFY = Format(iWeekOfFY, "00")

    strFY = Format(iTmpYear, "0000")

    ConvertDateToRawFYWeek = strFY & strWeekOfFY

End Function

Given a 4-5-4 calendar and a date, how do I determine what fiscal week that date falls in?

1 Answers