MS Access Convert dates to fiscal month number

I need to convert  dates  to fiscal month number. The fiscal year starts on 10/1.
shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
shieldscoAuthor Commented:
fiscal year number
Bill PrewIT / Software Engineering ConsultantCommented:
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
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

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)
shieldscoAuthor 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 ConsultantCommented:
shieldsco,

Right, your answer above said:

fiscal year number


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
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
shieldscoAuthor Commented:
Norie - returns the wrong fiscal year and month. I only need the fiscal month number
Bill PrewIT / Software Engineering ConsultantCommented:
For just fiscal month see my last two formulas, one zero pads to two digits, the other is just a month number.


»bp
shieldscoAuthor Commented:
Bill - look like the wrong results

M: (Month([MyDate]) Mod 10)+1
shieldscoAuthor 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
shieldscoAuthor Commented:
Bill - user defined

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

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Sorry, typo, should have been:

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


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
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
shieldscoAuthor 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
shieldscoAuthor Commented:
Bill all of your IIF statements are incorrect
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
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
Gustav BrockCIOCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.