MS Access Convert Date Field To Fiscal Year and Fiscal Quarter

I am not sure about the representation but

YearF = Year(date)

Select allOpen in new window

quarter

Quarter =datepart("q",date())

Select allOpen in new window

This should get them for you:

SELECT [MyDate], Year(DateAdd("m",3,[MyDate])) AS y, Datepart("q",DateAdd("m",3,[MyDate])) AS q FROM Table7;

Select allOpen in new window

---

»bp

You're going to need two custom functions:  one to accept a date and return the fiscal year of that date, another to accept a date and return the fiscal quarter of that date:

Public Function FiscalYear(dtDate As Date) As Integer
'author:  Paul Cook-Giles, 7/29/2019
Dim dtYearStart As Date

'identify first day of the fiscal year:
dtYearStart = DateSerial(Year(dtDate), 10, 1)

'if the date passed to the function is before the first day, the fiscal year is the same as the CE year
If dtDate < dtYearStart Then
    FiscalYear = Year(dtDate)
Else
'the date passed is on or after the first day of the next fiscal year;  increment Year by 1
    FiscalYear = Year(dtDate) + 1
End If

End Function

Select allOpen in new window

Public Function FiscalQuarter(dtDate As Date) As Integer
'author:  Paul Cook-Giles, 7/29/2019
Dim intQuarter As Integer

'If q = 1 Then fq = 3
'If q = 2 Then fq = 4
'If q = 3 Then fq = 1
'If q = 4 Then fq = 2

'identify first fiscal quarter
intQuarter = 4

'if date passed to the function has a Quarter property less than the first fiscal quarter
If DatePart("q", dtDate) < 3 Then
    'add 2 to the date's Quarter
    FiscalQuarter = DatePart("q", dtDate) + 2
Else
    'subtract 2 from the date's Quarter
    FiscalQuarter = DatePart("q", dtDate) - 2
End If

End Function

Select allOpen in new window

Give those a spin, and let us know if they don't work as expected.  :)

Thanks

As for the 10/01/Year Check

Public Function checkDate(inDate) As Date

If inDate > DateSerial(Year(inDate), 1, 1) And inDate < DateSerial(Year(inDate), 1, 10) Then
inDate = #10/1/2019#
End If
checkDate = inDate
End Function

Select allOpen in new window

I would likely use a technique similar to Paul's, but because fiscal year and calendar year are not necessarily the same, you would need some way to indicate when the FY starts, and what value to use.

For example, the USA govt's fiscal year begins on 1 October, and the FY value is the value of the upcoming calendar year, but some start on April 1st, others July 1st, and the year associated with that date might not always be the same, so you might want to add a FYStartDate to the function, and an indicator (0 or 1) of whether to use the current year or the next, so

? FiscalYear (#11/1/2019#, #10/1/2000#, 1) => 2020
?FiscalYear (#4/10/2019#, #4/1/2000#, 0) => 2000
?FiscalYear(#4/10/2019#, #4/1/2000#, 1) => 2020

Based on Paul's code this addition should be relatively simple.

You might even want to use a global variable (tempvar) to store the actual FY start, as it could change.