Day of Fiscal Year

[jsamstad]

Hello!

Our fiscal year begins 02-01 (February 1st). I'm trying to find the day of our fiscal year so that 02-01 is 1 and 01-31 is 365, then start over. 

Thank you in advance!

[Bobbyt23]

You could try using a date diff formula between the date field and the 1st of Feb for the year needed.

[Carolyn · Accepted solution]

Expanding on what @Bobbyt23 said - 

1. Do a Generate Rows to get a bunch of Dates. In this case, I did 2/1/24 - 1/31/26 = 2 FYs
2. Do a DateTimeDiff from 2/1 as suggested
   1. Here's where the fun part comes in - if the month is Feb - Dec, we want to do the DateTimeDiff using the same year (e.g. if it's 10/5/24, we want to compare to 2/1/24). We just use the same year from the Date and do the DateTimeDiff
   2. If the month is Jan, we want to compare to the prior year. So I find the Year in the Date (e.g. 1/5/25 -> year is 2025) and then subtract 1 (2025 - 1 = 2024) and stick that onto the 2/1 starting date. I do a DateTimeDiff where I'm subtracting 2/1/24 & 1/5/25 to get 340 days

For extra credit, you can also expand that logic to identify the Fiscal Year. At my company, our FY runs the same as yours. We identify the FY based on the last month. 2/1/24 - 1/31/25 = FY25

You can use the same logic to identify the FY. If it's Feb - Dec, I want to take the year and add 1 to the year to get the FY. If it's Jan, I just use the year

 

[jsamstad]

Yes!!! And well explained.  😀