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

Shreeraj
Shreeraj ✭✭
edited 09/22/22 in Formulas and Functions

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.

Best Answer

  • SolutionSal
    SolutionSal Employee
    edited 09/01/22 Answer ✓

    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!

Answers

  • 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.

  • Hi @SolutionSal

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


  • SolutionSal
    SolutionSal Employee
    edited 09/01/22 Answer ✓

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!