Created (Date) & weekday formula

Options

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.

Best Answer

  • Khari Shiver
    Khari Shiver ✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Khari Shiver
    Khari Shiver ✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Khari Shiver
    Khari Shiver ✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Khari Shiver
    Khari Shiver ✭✭✭✭✭
    Answer ✓
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!