MS Access Convert dates to fiscal month number

shieldsco
shieldsco used Ask the Experts™
on
I need to convert  dates  to fiscal month number. The fiscal year starts on 10/1.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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

Author

Commented:
fiscal year number
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

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

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

Open in new window


»bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NorieAnalyst Assistant

Commented:
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)

Author

Commented:
Bill - returns the fiscal year not the fiscal month number

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

Open in new window

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
shieldsco,

Right, your answer above said:

fiscal year number


»bp
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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

Author

Commented:
Norie - returns the wrong fiscal year and month. I only need the fiscal month number
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

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


»bp

Author

Commented:
Bill - look like the wrong results

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

Author

Commented:
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
NorieAnalyst Assistant

Commented:
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

Author

Commented:
Bill - user defined

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

Open in new window

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Sorry, typo, should have been:

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


»bp
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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

Open in new window


»bp

Author

Commented:
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

Author

Commented:
Bill all of your IIF statements are incorrect
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Okay, I think this is what will be needed...

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

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

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

M: (Month([MyDate]) Mod 10)+IIf(Month([MyDate])<10,3,1)

Open in new window


»bp
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

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

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

Open in new window


»bp
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Open in new window

Then, plain and simple:

FiscalDate = DateFinancial(AnyDate)

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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial