Setting a date based upon another date

Hi everyone, long time Smartsheet user first time poster here.

I am seeking help in setting a date column based upon another date column.

Put simply, I have a "date received" column and a "created" column, where the created column populates automatically based upon the time a submission was made using a form. The "date received" column I have been manually setting, as any work received after close of business (5:00pm) we consider as being received the following day.

So I'm seeking help in writing a formula that essentially says "If (created) is greater than 5:00pm and less than 11:59pm, set (date received) to tomorrow. If (created) is earlier than 5:00pm, set (date received) to today".

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...

    Create a helper column that is text/number and enter this formula...

    =SUBSTITUTE([Created Date]@row, DATEONLY([Created Date]@row), "")


    Then in the date column we would use...

    =DATEONLY([Created Date]@row) + IF(AND(VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1)) <> 12, VALUE(LEFT([Helper Column]@row, FIND(":", [Helper Column]@row) - 1))>= 5, FIND("P", [Helper Column]@row)> 0), 1)


    Basically if we find a "P" and the hour is greater than 5 but not equal to 12 (accounts for 12pm/noon), then add 1 to the Created Date.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/12/21 Answer ✓

    Hey Dan and Paul

    I believe this is the same timezone problem I encountered earlier last year in a few of my sheets. I found between the time of midnight of timezone GMT/UTC=0 (which is how the timestamp is actually stored) and midnight of the local time, the DATEONLY(Created@row), "") portion of the timestamp cannot be found since it's a different day.

    For the time portion of the formula, you can use =RIGHT(Created@row, (LEN(Created@row) - 9)) and it will work.

    The date is trickier - you can use the LEFT function and do the same thing but the date returned is the GMT=0 date, not necessarily the local time date. If that doesn't matter, use the LEFT function. If it does matter, you might want to consider adding a helper Date column and using the new Record a Date automation, triggering off of either a new row or changing value in Created field (which is essentially same thing). You could then use the Paul's earlier formula which changes the date based on 5pm, but looking at this new helper date column as your Date source.

    PS- since I was needing a solution prior to the Record a Date automation, I was able to use Bridge (premier type App from Smartsheet) to push the correct local date into a helper column on my sheet.

    cheers,

    Kelly

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!