EOMONTH to Calculate Fiscal Month/Year - Error

[GregStewartPTC]

I've been using a formula for the last 6 months or so to automatically calculate the fiscal month and year for a graph point based on the date of the raw data as well as the previous week's date. When updating the data for January I've run into an issue with the formula. It is calculating as fiscal '20 rather than fiscal '19, and changing the excel date did not correct the issue as I'd thought it would. I've attached an example spreadsheet, where M4 & N4 contain the formula (also pasted below), to show the issue in N4. The formula is reading the previous and current week's date in M5 & N5, respectively. Any help is appreciated. Thank you!

Please Login or Register  to view this content.

[FlameRetired]

I have difficulty following the narrative and relating it to the upload.

I suspect that formula can be simplified and shortened, but I am having difficulty relating it to the intent so I can't say for sure.

Let's start here. Specifically what dates to you want returned in M4:N4?

[Roel Jongman]

To some time to analyse ist
In the lookup the problem is caused by taking the year from the prevoious week. while the lookup formula starts with 4 and has 9 added on jan fith that pushes the date 13 months ahead from 2018 which is jan 20.
You would need another test that when the calander year changes over, you need to reduce year(n5) by 1 to compensate for adding 9 to 4.

But to be honest I think the formula looks overcomplated. I like formula's that work always without manual intervention but in this case I would use a simple lookup table because every fiscal year is normally kept in separate sheets so some yearly setup is acceptable if it greatly simplefy a formula. The rules for fiscal year are standard each year all that changes is the year and the weekday it starts on. when you have this table setup all you need to do once a year is change the fiscal year in a fixed cell to update the table.

then a simple lookup would find the fiscal month

this is my formula

Please Login or Register  to view this content.

see attachment for the table in T:V columns.

[Root_]

@ GregStewartPTC:

A couple notes on your existing formula:

1. If I understand it correctly, the formula is tailored for a fiscal year of May 2018 - Apr 2019 and it will not work beyond that range.

2. If I understand it correctly, there is a week-count error in your formula: the first array constant should be {1,5,10,14,18,22,27,31,35,40,44,48,53}.

Please see if the following formula works for you. It should be good for any year:

Please Login or Register  to view this content.