Public MyTest As Variant
Public Function FY(Optional MyDate As String, Optional StartMonth As Integer) As Integer

'   This function determines the financial year of a date

'   It allows the start month of the financial year to be specified.
'   if omitted October is used (10).

Dim WorkDate As Date

If IsMissing(StartMonth) Then
    StartMonth = 10     ' <=== Change this to set a different default start month
End If
If StartMonth = 0 Then
    StartMonth = 10     ' <=== Change this to set a different default start month
End If
If MyDate = "" Then
    MyDate = Date
End If
If InStr(1, MyDate, "/") = 0 Then
    If Len(MyDate) <> 8 Then
        MsgBox "Function FY" & vbCrLf & vbCrLf & "Length error in SAP date"
        FY = 911
        Exit Function
    End If
    MyTest = FYValidateSAP(MyDate)
    If Left(MyTest, 9) = "*INVALID*" Then
        MsgBox "Function FY" & vbCrLf & vbCrLf & MyTest
        FY = 911
        Exit Function
    End If
    tempstring = Mid(MyDate, 5, 2) & "/" & Right(MyDate, 2) & "/" & Left(MyDate, 4)
    WorkDate = DateValue(tempstring)
    MyDate = WorkDate
Else
    Count = Len(MyDate) - Len(Replace(MyDate, "/", ""))
    If Count <> 2 Then
        MsgBox "Function FY" & vbCrLf & vbCrLf & "Format error in date"
        FY = 911
        Exit Function
    End If
    WorkDate = DateValue(MyDate)
End If
If Month(MyDate) >= StartMonth Then
    If Month(WorkDate - Weekday(WorkDate, vbUseSystem) + 1) >= StartMonth Then
        FY = Right(Year(MyDate), 2) + 1
    Else
        FY = Right(Year(MyDate), 2)
    End If
Else
    FY = Right(Year(MyDate), 2)
End If
End Function
Public Function FYWeek(Optional MyDate As String, Optional StartMonth As Integer) As Integer

'   This function determines the week of the financial year of a date

'   It allows the start month of the financial year to be specified.
'   if omitted October is used (10).


Dim YearStart As Date
Dim OurYear As Date
Dim WorkDate As Date
If IsMissing(StartMonth) Then
    StartMonth = 10     ' <=== Change this to set a different default start month
End If
If StartMonth = 0 Then
    StartMonth = 10     ' <=== Change this to set a different default start month
End If
If MyDate = "" Then
    MyDate = Date
End If
If InStr(1, MyDate, "/") = 0 Then
    If Len(MyDate) <> 8 Then
        MsgBox "Function FYWeek" & vbCrLf & vbCrLf & "Length error in SAP date"
        FYWeek = 911
        Exit Function
    End If
    MyTest = FYValidateSAP(MyDate)
    If Left(MyTest, 9) = "*INVALID*" Then
        MsgBox "Function FYWeek" & vbCrLf & vbCrLf & MyTest
        FYWeek = 911
        Exit Function
    End If
    tempstring = Mid(MyDate, 5, 2) & "/" & Right(MyDate, 2) & "/" & Left(MyDate, 4)
    WorkDate = DateValue(tempstring)
    MyDate = Workdate
Else
    Count = Len(MyDate) - Len(Replace(MyDate, "/", ""))
    If Count <> 2 Then
        MsgBox "Function FYWeek" & vbCrLf & vbCrLf & "Format error in date"
        FYWeek = 911
        Exit Function
    End If
    WorkDate = DateValue(MyDate)
End If
If Month(MyDate) < StartMonth Then
    OurYear = MyDate
    OurYear = DateSerial(Year(MyDate) - 1, Month(MyDate), Day(MyDate))
    Our1Year = Year(OurYear)
Else
    Our1Year = Year(MyDate)
End If
Year1start = StartMonth & "/01/" & Our1Year
YearStart = DateValue(Year1start)
For x = 1 To 7
    If Month(YearStart - Weekday(YearStart, vbUseSystem) + 1) = Month(YearStart) Then
        x = 7
    Else
        YearStart = DateAdd("d", 1, YearStart)
    End If
Next
FYWeek = DateDiff("ww", YearStart, MyDate) + 1
If FYWeek <= 0 Then
    FYWeek = FYWeek * -1
    FYWeek = 52 - FYWeek
End If
End Function
Public Function FYPeriod(Optional MyDate As String, Optional StartMonth As Integer) As Integer

'   This function determines the month period of the financial year of a date

'   It allows the start month of the financial year to be specified.
'   if omitted October is used (10).

'   It assumes the  pattern of 4, 5, 4, 4, 5, 4, 4, 5, 4, 4, 5, 4 weeks in a period

If IsMissing(StartMonth) Then
    StartMonth = 10     ' <=== Change this to set a different default start month
End If
If MyDate = "" Then
    MyDate = Date
End If
Dim Loop4Control As Integer
Dim Minus As Integer
Dim WeekNo As Integer
FYPeriod = 0
Loop4Control = 1
If InStr(1, MyDate, "/") = 0 Then
    If Len(MyDate) <> 8 Then
        MsgBox "Function FYPeriod" & vbCrLf & vbCrLf & "Length error in SAP date"
        FYPeriod = 911
        Exit Function
    End If
    Dim MyTest As String
    MyTest = FYValidateSAP(MyDate)
    If Left(MyTest, 9) = "*INVALID*" Then
        MsgBox "Function FYPeriod" & vbCrLf & vbCrLf & MyTest
        FYPeriod = 911
        Exit Function
    End If
    tempstring = Mid(MyDate, 5, 2) & "/" & Right(MyDate, 2) & "/" & Left(MyDate, 4)
    WorkDate = DateValue(tempstring)
    MyDate = WorkDate
Else
    Count = Len(MyDate) - Len(Replace(MyDate, "/", ""))
    If Count <> 2 Then
        MsgBox "Function FYPeriod" & vbCrLf & vbCrLf & "Format error in date"
        FYPeriod = 911
        Exit Function
    End If
    WorkDate = DateValue(MyDate)
End If
WeekNo = FYWeek(MyDate, StartMonth)
For x = 1 To 12
    FYPeriod = FYPeriod + 1
    If x = 1 Then
        Loop4Control = 3
        Minus = 4
    Else
        If Loop4Control < 3 Then
            Minus = 4
            Loop4Control = Loop4Control + 1
        Else
            Loop4Control = 1
            Minus = 5
        End If
    End If
    If WeekNo - Minus <= 5 Then
        If Minus = 4 Then
            If WeekNo - Minus <= 0 Then
                x = 12
            Else
                WeekNo = WeekNo - Minus
                FYPeriod = FYPeriod + 1
                x = 12
            End If
        Else
            If WeekNo - Minus <= 0 Then
                x = 12
            Else
                WeekNo = WeekNo - Minus
                FYPeriod = FYPeriod + 1
                x = 12
            End If
         End If
    Else
        If WeekNo - Minus > 4 Then
            WeekNo = WeekNo - Minus
         End If
    End If
Next
End Function
Public Function FYValidateSAP(MyDate As String) As String
If MyDate = "" Then
    FYValidateSAP = "*INVALID* Missing"
Else
    If Len(MyDate) <> 8 Then
        FYValidateSAP = "*INVALID* Length"
    Else
        If Len(MyDate) - Len(Replace(MyDate, "/", "")) <> 0 Then
            FYValidateSAP = "*INVALID* Format"
        Else
            If Mid(MyDate, 5, 2) > 12 Then
                FYValidateSAP = "*INVALID* Month"
            Else
                FYValidateSAP = Mid(MyDate, 5, 2) & "/" & Right(MyDate, 2) & "/" & Left(MyDate, 4)
                If Not IsDate(FYValidateSAP) Then
                    FYValidateSAP = "*INVALID* Date. Please use yyyymmdd"
                End If
            End If
        End If
    End If
End If
End Function