WeekNumber & WeekDay error

Options
Nikki_Glass
Nikki_Glass ✭
in Formulas and Functions

Can anyone help with a way round the weeknum formula that starts on a Monday rather than a Sunday?

I'm using the WeekNumber formula to get a current week in conjunction with the WeekDay formula to create a list of tasks due in a current week sorted by days. Because the dates for when each task is due is auto populated based on a delivery date, sometimes the tasks end up being assigned to a Saturday or Sunday.

For example a task due on Sunday 29/05/22 is showing as a Current week task and is assigned to week day 1. Therefore it's showing in the task list for Monday 23/05/22.

Any suggestions please?

· Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Nikki_Glass

    Would you want the Saturday to also move to the next Monday? So the WeekNumber starts on the Saturday of the week?

    This formula will start the week on the Sunday:

    =IF(WEEKDAY(Date@row) = 1, WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))


    You could include Saturday in the formula by checking for Weekday 7 as well as 1:

    =IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))

    Screen Shot 2022-05-26 at 1.09.10 PM.png

    Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • Nikki_Glass
    Nikki_Glass ✭
    Options

    Thanks so much that's sorted it.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!