How to auto-populate my companies Fiscal Year week number if its a 52 week year?

Shreeraj

My companies FY starts 8/1 and ends 7/30.

Currently I am using =ROUND(((Created@row - DATE(2021, 8, 1)) / 7) + 0.49, 0) which works perfectly but it shows 56 weeks instead of a 52 week FY.

SolutionSal

I believe that error occurs because the [Create] column is a 'Created Date' column type. The WEEKNUMBER function is expecting a MM/DD/YY date without a HH:MM time value. My bad, I tested my formula with [Created} set to 'Date' column type. I also noticed my original formula was missing the WEEKNUMBER function around the second and third date values.

My solution for extracting just the date from the date/time value in [Created] is to add a [Just Date] helper column with 'Date' as the column type, and add a column formula of: "=Created@row ". Then change my original formula to

=IF((WEEKNUMBER([Just Date]@row) + 22) > 52, (WEEKNUMBER([Just Date]@row) + 22) - 52, WEEKNUMBER([Just Date]@row) + 22)

Hope this helps!

SolutionSal

The function you're using is clever. However I think there may be a simpler solution. Are you familiar with the WEEKNUMBER function (https://help.smartsheet.com/function/weeknumber)? I'm thinking you could calculate the WEEKNUMBER then add or subtract an 'offset' to fit your FY dates.

=IF( (WEEKNUMBER(Created@row) + 22) > 52, (Created@row + 22) - 52, Created@row + 22)

This should return a week number within the right range.

Shreeraj

Hi @SolutionSal

I had tried the formula you had given and I am getting an #invalid column value error.

SolutionSal

I believe that error occurs because the [Create] column is a 'Created Date' column type. The WEEKNUMBER function is expecting a MM/DD/YY date without a HH:MM time value. My bad, I tested my formula with [Created} set to 'Date' column type. I also noticed my original formula was missing the WEEKNUMBER function around the second and third date values.

My solution for extracting just the date from the date/time value in [Created] is to add a [Just Date] helper column with 'Date' as the column type, and add a column formula of: "=Created@row ". Then change my original formula to

=IF((WEEKNUMBER([Just Date]@row) + 22) > 52, (WEEKNUMBER([Just Date]@row) + 22) - 52, WEEKNUMBER([Just Date]@row) + 22)

Hope this helps!