Formula that translates a date to Fiscal Year, Fiscal Period, and Fiscal Week

0
votes

Working on a formula that will take a date and translate it to the format FYxxPxxWx.

For example. Input the date of 03/22/20 and the formula will give you FY20P06W4 which is correct.

However if you input 02/02/20 the formula will give you FY20P05W2. The correct output would be FY20P05W1. This issue also rears its head with the date 09/29/19. It gives you FY20P12W5. The correct output would be FY20P1W1.

Something else weird happens when you put in the date 04/5/20 you get FY21P07W2 when it should be FY20P07W2.

The formula is =CONCATENATE("FY",RIGHT(YEAR(DATE(YEAR(D5),MONTH(D5)+(10-1),1)),2),"P",TEXT(CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3),"0#"),"W",WEEKNUM(D5,1)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1),1)+1)

I think this issue is caused by the strange weeks where the the month ends and another begins throwing off the formula.

I do have a formula that calculates the years fiscal year start date

=(DATE(YEAR(TODAY())-1,10,1)-(WEEKDAY(DATE(YEAR(TODAY())-1,10,1),1)))+1

This outputs 09/29/19 as the start date of the Fiscal year as its the same week as 10/1/19 which is the first month of the fiscal year. IF that makes sense.

The separate formulas are For FY and grabs only last two digits of year RIGHT(YEAR(DATE(YEAR(D5),MONTH(D5)+(10-1),1)),2)

For Period (gives me a two digit Period TEXT(CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3),"0#")

For Week WEEKNUM(D5,1)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1),1)+1)

1
excel-formula
Can you help me understand what W represents please? – Mech
Week. Have Fiscal Year. Fiscal Period. Fiscal Week. We think the issue is because dates like 1/31/20 and 2/1/20 are on the same week. If we stayed with 1/31/20 it should read as FY20P05W1 even though its still part of January but it starts on the week of Feb 1st. – Kyle Drew
Sorry. How is it derived? – Mech
Do the individual formulas work on their own?. Ie if you break down the year in one column, period in the next column and week in its own column are they giving you the right numbers? Tends to help to break formulas down in to their smallest part to ID where things are going wrong. Alternatively have you stepped through the formula using formula evaluation in the ribbon? – Forward Ed
if you use =ISTEXT(D5) what do you get as a result? – Forward Ed

1 Answers

1
votes

I believe I have a solution for you. Discussion to follow, but here's the full formula:

=CONCAT("FY",RIGHT(YEAR(D5+91+WEEKDAY(DATE(YEAR(D5),10,1))),2),"P",TEXT(IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5),IF(MONTH(D5)=9,1,CHOOSE(MONTH(D5),5,6,7,8,9,10,11,12,1,2,3,4)),CHOOSE(MONTH(D5),4,5,6,7,8,9,10,11,12,1,2,3)),"0#"),"W",ROUNDUP(((D5-IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5),DATE(YEAR(D5),MONTH(D5)+1,1)-WEEKDAY(DATE(YEAR(D5),MONTH(D5)+1,1))+1,DATE(YEAR(D5),MONTH(D5),1)-WEEKDAY(DATE(YEAR(D5),MONTH(D5),1))+1))/7)+0.01,0))

One issue is that this still calculates 2/2/2020 the way you said was incorrect. When I verify it against a calendar, though, it seems that FY20P05W02 should be correct. If the week that includes the first of the month begins a new pay period, that would mean 2/1/2020, falling on a Saturday, would be the last day of fiscal week 1. That would make 2/2/2020 the first day of fiscal week 2.

To calculate fiscal year, I used RIGHT(YEAR(D5+91+WEEKDAY(DATE(YEAR(D5),10,1))),2). Since you can count on there always being 91 days from the beginning of October to the end of December, it helps with this calculation. In your formula, you had MONTH(D5)+(10-1), which would push you 9 months out past the month in D5. This explains why your result for 4/5/2020 was off by a year.

Fiscal period was a bit trickier, requiring a couple nested IF statements. I used IF(MONTH(D5+(7-WEEKDAY(D5)))<>MONTH(D5) first to account for days at the end of the month that would fall into the next fiscal period, then IF(MONTH(D5)=9 to account for the few days at the end of September that might fall into the next fiscal year. Days at the end of September would default to 1, days at the end of a month that are included in the next fiscal period use the first CHOOSE function (they need the next month's number), and everything else gets the CHOOSE function as you wrote it.

The fiscal week took a bit more, but in the end I evaluated the beginning of the current fiscal month and subtracted it from the date in D5, then divided by 7 and added 0.01 so that even numbers would round up correctly.

I tested this out over a few years of dates and it seemed to be functioning correctly, but let me know if you have questions or issues.

One thing to consider when using WEEKNUM is that you'll have a week that is counted twice at the beginning of the year unless you use option 21 or ISOWEEKNUM. These give the same result as each other, and ensure that only one week number is assigned to any given day, no matter the year.