MS Access Convert dates to fiscal month number

Do you just want the month number, 1 - 12, or also the fiscal year as well as part of that?  If so what format, YYYYMM, or something else?

---

»bp

fiscal year number

You can calcluate fiscal year in a query with the formula:

=IIF(MONTH(YourDate)<10,YEAR(YourDate),YEAR(YourDate)+1)

Select allOpen in new window

---

»bp

Perhaps something like this, though I'm sure there's a better way.

FiscalYearMonth:Iif(Month([YourDateField])>9, Month([YourDateField])-9&"-"&Year([YourDateField]), Month([YourDateField])+3&"-"&Year([YourDateField])-1)

Bill - returns the fiscal year not the fiscal month number

=IIF(MONTH(YourDate)<10,YEAR(YourDate),YEAR(YourDate)+1)

Select allOpen in new window

shieldsco,

Right, your answer above said:

fiscal year number

---

»bp

Okay, here are the pieces, use as needed:

YYYYMM: IIf(Month([MyDate])<10,Year([MyDate]),Year([MyDate])+1) & Right$("0" & (Month([MyDate]) Mod 10)+1,2)

YYYY: IIf(Month([MyDate])<10,Year([MyDate]),Year([MyDate])+1)

MM: Right$("0" & (Month([MyDate]) Mod 10)+1,2)

M: (Month([MyDate]) Mod 10)+1

---

»bp

Norie - returns the wrong fiscal year and month. I only need the fiscal month number

For just fiscal month see my last two formulas, one zero pads to two digits, the other is just a month number.

---

»bp

Bill - look like the wrong results

M: (Month([MyDate]) Mod 10)+1

Bill your results are incorrect
Term Date      M      Correct
28-Oct-18      1      
10-Nov-18      2      
03-Jan-19      2      4
28-Oct-18      1      
14-Oct-18      1      
03-Jan-19      2      4
10-Nov-18      2      
03-Jan-19      2      4
03-Jan-19      2      4
14-Oct-18      1

Here are some results from my expression, with the fiscal month/year in MM-YYYY format.

21 June 2018            09-2017
11 July 2018            10-2017
20 August 2018            11-2017
20 September 2018            12-2017
07 October 2018            01-2018
09 November 2018            02-2018
18 December 2018            03-2018
02 January 2019            04-2018
03 February 2019            05-2018
18 March 2019            06-2018
08 April 2019            07-2018
18 May 2019            08-2018
27 June 2019            09-2018
25 July 2019            10-2018
17 August 2019            11-2018
13 September 2019            12-2018
25 October 2019            01-2019
17 November 2019            02-2019
11 December 2019            03-2019
21 January 2020            04-2019
03 February 2020            05-2019
14 March 2020            06-2019
18 April 2020            07-2019
18 May 2020            08-2019
14 June 2020            09-2019
04 July 2020            10-2019
15 August 2020            11-2019
25 September 2020            12-2019
08 October 2020            01-2020
20 November 2020            02-2020

Bill - user defined

Right$("0" &  (Month([Term Date]) Mod 10)+1,2)

Select allOpen in new window

Sorry, typo, should have been:

M: (Month([MyDate]) Mod 10)+3

---

»bp

So the full list looks like:

YYYYMM: IIf(Month([MyDate])<10,Year([MyDate]),Year([MyDate])+1) & Right$("0" & (Month([MyDate]) Mod 10)+3,2)

YYYY: IIf(Month([MyDate])<10,Year([MyDate]),Year([MyDate])+1)

MM: Right$("0" & (Month([MyDate]) Mod 10)+3,2)

M: (Month([MyDate]) Mod 10)+3

Select allOpen in new window

---

»bp

Bill it still incorrect
FullName      Term Date      M      Correct
Whang, Jennifer      10/28/2018      3      1
Turner, Sharon      11/10/2018      4      2
Smith, Gary      1/3/2019      4      
Ramaley, Sean      10/28/2018      3      1
Malvin, Daniel      10/14/2018      3      1
Smith, Gary      1/3/2019      4      
Turner, Sharon      11/10/2018      4      2
Smith, Gary      1/3/2019      4      
Smith, Gary      1/3/2019      4      
Malvin, Daniel      10/14/2018      3      1

Bill all of your IIF statements are incorrect

And Norie's approach below seems equally valid to me...

M: IIf(Month([MyDate])<10,Month([MyDate])+3,Month([MyDate])-9)

Select allOpen in new window

---

»bp

You are making this way too convoluted. Use a simple helper function, and the fiscal dates can be handled like normal date values:

Public Function DateFinancial( _
  ByVal datDate As Date) _
  As Date
  
  ' Number of months from start of calendar year to start of financial year.
  Const clngMonthOffset As Long = 9
  
  Dim datFinancial      As Date
  
  datFinancial = DateAdd("m", -clngMonthOffset, datDate)
  
  DateFinancial = datFinancial
  
End Function

Select allOpen in new window

Then, plain and simple:

FiscalDate = DateFinancial(AnyDate)

YYYYMM: Format(FiscalDate, "yyyymm")
YYYY: Year(FiscalDate)
MM: Format(FiscalDate, "mm")
M: Month(FiscalDate)

Select allOpen in new window