WeekNumber & WeekDay error

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?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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))

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

    Cheers,

    Genevieve

  • Thanks so much that's sorted it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!