WEEKNUMBER and WEEKDAY

Michael Palko

I've built a Smartsheet (with some help from the ProDesk) that assigns a task to a contact based on the date/time of a request. The sheet looks at the date stamp on the request then references another sheet (using WEEKNUMBER and WEEKDAY) to see who's "on duty" at that time. That contact's name is pulled into an "Assigned" column and they receive a notification.

Everything was working as expected, until a request came in on a Sunday. At that time Smartsheet assigned the contact who had been on duty the PREVIOUS Sunday. Here's why:

Smartsheets gives a numeric value to WEEKDAYS from Sunday to Saturday (i.e.  1=Sunday, 2=Monday, etc.). While Smartsheets enumerates the weekdays from Sunday to Saturday, the function for WEEKNUMBER looks at a week (as does most of the business world) from Monday to Sunday.

That means that if you want to assign a task on Sunday of Week 4 (WEEKNUMBER 04 / WEEKDAY 01), that date, according to Smartsheets, is 01/26/2020 which comes after Saturday of Week 4 (WEEKNUMBER 04 / WEEKDAY 07) 01/25/2020 and not before Monday of Week 4.

I worked around this, to get the correct contact assigned, but laying out my "on duty" Smartsheet to look like this:

There was also an added wrinkle in that if the request came in after 4PM on Sunday, I wanted to assign it to the contact on duty on Monday. To accomplish that I, of course, had to look at the time in the Date Stamp, but also add 1 to the WEEKNUMBER (=IF([WEEKDAY]@row = 1, WEEKNUMBER(Date@row + 1), WEEKNUMBER(Date@row)).

This may never be an issue for you if you only assign tasks from Monday through Friday, but for those who have a 7-day operation, hopefully this will save you some work.

Have you ever run into this?

Lisa Vercellone