Created (Date) & weekday formula

Khari Shiver

Hello,

I've created a form that has a date/time stamp i.e. "created (date)" value. That value is automatically added to my sheet and I would like to create an automation that sends an email if the request is submitted on a weekend. I'm trying to use the weekend function but it is behaving strangely.

There is a date of 5/10/21 5:50pm that the weekday function is somehow recognizing as a Tuesday. It is my understanding that it begins with Sunday as a 1 and so on, which would make 5/10 a 2. I could sort of understand if it was based on the servers being on EST and the time was after 9pm but that's not the case.

Any help would be greatly appreciated.

Thanks.

Khari Shiver

Smartsheet support explained the issue. The time/date stamp is stored on their servers at UTC time but the view we see is based on the time zone the user is in. There is a 7 hour time difference between where I am and the UTC time which is why anything after 5pm is recognized as the next day by the Weekday formula. Good to know.

Thank you for assisting me.

Mark Cronk

Hi @Khari Shiver ,

Try combining DATEONLY and WORKDAY:

=IF(OR(WEEKDAY(DATEONLY([new hire form submitted]@row))=1, WEEKDAY(DATEONLY([new hire form submitted]@row)) =7), "Weekend","")

Work?

Mark

Khari Shiver

Did you mean "WEEKDAY" or "WORKDAY" for use in the formula?. I tried it as written with weekday and got a blank. I changed your formula and substituted a 3 for a 1 and the formula returned a value of "Weekend" which means it's somehow returning a 3 i.e. recognizing 5/10 as Tuesday. I received the same value when using just the Weekday formula. It's strange.

Your slightly altered formula:

=IF(OR(WEEKDAY(DATEONLY([New Hire Form Submitted]@row)) = 3, WEEKDAY(DATEONLY([New Hire Form Submitted]@row)) = 7), "Weekend", "0")

The Weekday column formula: =WEEKDAY(DATEONLY([New Hire Form Submitted]@row)) is treating 5/10 like it's a Tuesday.

Column23 formula: =WEEKDAY([Column22]@row) is treating 5/10 correctly i.e. a Monday.

Your slightly altered formula is in Column25 is treating 5/10 like a Tuesday. I'm not sure what to make of it.

Mark Cronk

WEEKDAY() should return a number from 1 to 7 where 1 = Sunday. Info below. If WEEKDAY() is 7 or 1 it's a weekend. 2-6 are weekdays.

https://help.smartsheet.com/function/weekday

Try removing the dateonly: =IF(OR(WEEKDAY([New Hire Form Submitted]@row) = 3, WEEKDAY([New Hire Form Submitted]@row) = 7), "Weekend", "Weekday")

Mark

Khari Shiver

Your formula makes sense; the issue I'm having is with the WEEKDAY() function. It recognizes "05/10/21 5:50 PM" as a Tuesday which it isn't but recognizes "05/10/21" when formatted as a date as a Monday which is correct.

I tried using the LEFT() function to extract the "05/10/21" from the time/date stamp but I got an invalid data type error.

Mark Cronk

The DATEONLY () funtion removed the time. Does putting that back in fix the problem?

Mark

Khari Shiver

Smartsheet support explained the issue. The time/date stamp is stored on their servers at UTC time but the view we see is based on the time zone the user is in. There is a 7 hour time difference between where I am and the UTC time which is why anything after 5pm is recognized as the next day by the Weekday formula. Good to know.

Thank you for assisting me.