Thanks Tony Didn't quite work properly however...

...but someone at another forum has come up trumps with the following, which has been slightly "tweaked" for use in my DB, as well as for use by Excel:
[vba]
Public Function GetFinancialPeriod(dtDate As Date) As String

'DESCRIPTION / PURPOSE
'This function takes as a single required argument a date and will return a period and week number
'for the DC financial calendar. See Notes for the assumptions of this financial calendar
'
'INPUTS:
'1. A date (as the variable dtDate), representing the date for which the user wants to know the
' period and week number to which this date belongs.
'
'OUTPUTS:
'1. The period and week number of the input date as a string.
'
'NOTES:
'The financial calendar operates under these assumptions:
'1. The first day of the financial calendar is a Sunday.
'2. If 25 March of a given year is a Sunday, then 2 April is the start of the new financial year.
'3. If 25 March is NOT a Sunday, then the closest Sunday preceding or equalling 1 April is
' the first day of the financial calendar
'4. Weeks are 7 days in length.
'5. There are 12 periods per calendar year
'6. Periods 3, 6, and 9 are composed of 5 weeks.
'7. Periods 1-2, 4-5, 7-8, 10-11 are composed of 4 weeks.
'8. Normally, week 12 is 5 weeks. However, in the event 24 or 25 March is set to be the last
' day under this rule, then an extra week is added to Period 12 for a total of 6 weeks.

Dim lngDayDifference As Long, lngPeriodNumber As Long, lngWeekNumber As Long
Dim dtFirstDayOfFinancialYear As Date

'If 25 March of this year is Sunday, then 2 April is actually the first day of the financial year
If Weekday(CDate("25-Mar-" & Year(dtDate))) = vbSaturday Then
dtFirstDayOfFinancialYear = CDate("2-Apr-" & Year(dtDate))
Else
dtFirstDayOfFinancialYear = DateAdd("d", 1 - Weekday(CDate("1-Apr-" & Year(dtDate))), _
CDate("1-Apr-" & Year(dtDate)))
End If

If dtDate >= dtFirstDayOfFinancialYear Then
lngDayDifference = DateDiff("d", dtFirstDayOfFinancialYear, dtDate) + 1
Else
'If 25 March is Sunday, then 2 April is actually the first day of the financial year
If Weekday(CDate("25-Mar-" & Year(dtDate) - 1)) = vbSaturday Then
dtFirstDayOfFinancialYear = CDate("2-Apr-" & Year(dtDate) - 1)
Else
dtFirstDayOfFinancialYear = DateAdd("d", 1 - Weekday(CDate("1-Apr-" & Year(dtDate) - 1)), CDate("1-Apr-" & Year(dtDate) - 1))
End If
lngDayDifference = DateDiff("d", dtFirstDayOfFinancialYear, CDate("31-Dec-" & Year(dtDate) - 1)) + _
DateDiff("d", CDate("1-Jan-" & Year(dtDate)), dtDate) + 2
End If

lngPeriodNumber = 1
lngWeekNumber = 0
Do
lngWeekNumber = lngWeekNumber + 1
If lngWeekNumber = 6 And lngPeriodNumber < 12 Then
lngWeekNumber = 1
lngPeriodNumber = lngPeriodNumber + 1
ElseIf lngWeekNumber = 5 And Not (lngPeriodNumber = 3 Or lngPeriodNumber = 6 Or _
lngPeriodNumber = 9 Or lngPeriodNumber = 12) Then
lngWeekNumber = 1
lngPeriodNumber = lngPeriodNumber + 1
End If
lngDayDifference = lngDayDifference - 7 'always 7 days in a week
Loop While lngDayDifference > 0

'Optional Output for Excel - uncomment if not required...
GetFinancialPeriod = "Period " & lngPeriodNumber & ", Week " & lngWeekNumber

'Optional Output for Access - use ParseArgString function to recover information - uncomment if necessary
strFinancialPeriod = "&Period=" & lngPeriodNumber & "&Week=" & lngWeekNumber

End Function[/vba]

Just in case anyone ever needs a similar function....

Ad