Work Week Formula

Adriane Price

Hello,

This may have already been asked however I am not seeing it in the threads, I am looking for a version of this formula =(WEEKNUMBER([Date Entered]@row)-5) to function properly so that it shows the work week (screenshot) :

Previously it worked without any issues and showed the 'Work Week', now it is giving me a -4:

Please let me know the error of my ways and why it is now not working.

Thank you!

Leibel S

@Adriane Price

A simpler solution would be to deduct days from the date calculated in your WEEKNUMBER formula.

So =WEEKNUMBER([Date Entered]@row -35) will return the smartsheet week of 35 days before date entered...

Nic Larsen

I suspect that Smartsheet doesn't know how to subtract Weeks to a prior year. Since you're in Week 1 of 2021, it doesn't know how to subtract weeks back to 2020.

Is there a reason to subtract 5 from the Weeknumber formula?

If that is needed rather than just determining the actual week of the year, you'd likely need to add conditions to your formula to 1st check if the week number is for the 1st 5 weeks of a new year to say IF WeekNumber is 1, subtract 4 from 53, if 2, subtract 3 from 53 and so on. Once you move beyond that, it should function properly until you get to next year.

Adriane Price

@Nic Larsen - thank you for responding, we are actually in week 49 of our fiscal year. However, after further review I found an old post and I think this formula did the trick.

=IF((WEEKNUMBER([Date Entered]@row) - 5) < 0, WEEKNUMBER([Date Entered]@row) + 48, WEEKNUMBER([Date Entered]@row) - 5)

Leibel S

@Adriane Price

A simpler solution would be to deduct days from the date calculated in your WEEKNUMBER formula.

So =WEEKNUMBER([Date Entered]@row -35) will return the smartsheet week of 35 days before date entered...