Fiscal Month Formula

Stilwellj

I have been upgraded from excel to Smart Sheet and most of my formulas are able to be translated into Smart Sheet. I am having an issue with taking a date that someone enters and converting into our fiscal. The fiscal months are the same as the calendar month but the month starts on the 22nd of the prior month. So for example Fiscal January 2024 started on 12/22/2023 and will end on 1/21/2024. Below is the formula I use for excel and I am getting a head ache trying to convert it. The output is 2024-01 as an example. Any help would be great. Thank you.

=TEXT(DATE(YEAR([DATE CELL]-21),MONTH([DATE CELL]-21)+1,1),"YYYY-MM")

Stilwellj

I was able to build out an option that works. Here it is if anyone needs to duplicate.

=IF(MONTH(Date@row) = 12, IF(DAY(Date@row) > 21, YEAR(Date@row) + 1, YEAR(Date@row)), YEAR(Date@row)) + "-" + IF(MONTH(Date@row) = 12, IF(DAY(Date@row) > 21, MONTH(Date@row) - 11, MONTH(Date@row)), IF(DAY(Date@row) > 21, MONTH(Date@row) + 1, MONTH(Date@row)))

=Chris Palmer

Hello @Stilwellj

Smartsheet doesn't support =TEXT currently.

The formula below may suffice however, just replace "Date@row" with the actual cell you're referencing.

Be sure to make sure the properties of the date column are set to Date in Smartsheet as well.

=YEAR(Date@row - 21) + "-" + IF(MONTH(Date@row - 21) < 10, "0" + MONTH(Date@row - 21), MONTH(Date@row - 21))

Let me know if this works for you.

Stilwellj

@Mr. Chris That changes the dates but shows the fiscal prior i.e. what should be 2023-11 shows 2023-10.

Stilwellj

I was able to build out an option that works. Here it is if anyone needs to duplicate.

=IF(MONTH(Date@row) = 12, IF(DAY(Date@row) > 21, YEAR(Date@row) + 1, YEAR(Date@row)), YEAR(Date@row)) + "-" + IF(MONTH(Date@row) = 12, IF(DAY(Date@row) > 21, MONTH(Date@row) - 11, MONTH(Date@row)), IF(DAY(Date@row) > 21, MONTH(Date@row) + 1, MONTH(Date@row)))