Working with Date/Time

Question

Hello,

I am aware smartsheet has some limitations when using Date/Time functions, but would really appreciate any help.

I am working with inputs from a form, where smartsheet is providing the Auto-Number/System Date and Time of a form input. I need to check if the time portion of this cell is before or after 4:00PM to trigger a workflow action. Is there any way I can setup a formula which could result in a "Yes/No" output to be used as a workflow condition?

Also, does smartsheet have any functionality related to Business Days like in Excel?

Thank you.

Jeff Reisman · Accepted Answer

@C.M.

I always tell myself I'm not going to get involved with time formulas in Smartsheet, but I never listen!

Assuming your datetime system column is called "Created".

We'll first need to get the Hour as a numeric value by itself:

=VALUE(IF(CONTAINS(":", MID(Created@row, 10, 2)), MID(Created@row, 10, 1), MID(Created@row, 10, 2)))

This looks at the hour portion of the date time stamp, starting at position 10 from the left and taking the tenth and eleventh characters. If it finds ":" in there, that means the hour is one digit, so then it just wants the tenth character, otherwise, take both characters (hour 11 or 12.) The VALUE wrapper converts the result to its numeric value rather than remaining text. This is important because you can't do math against text.

Next we need to figure out AM vs PM:

=RIGHT(Created@row, 2)

This pulls the rightmost two characters from the datetime stamp.

Now let's put it all together:

=IF(AND(VALUE(IF(CONTAINS(":", MID(Created@row, 10, 2)), MID(Created@row, 10, 1), MID(Created@row, 10, 2))) >= 4, VALUE(IF(CONTAINS(":", MID(Created@row, 10, 2)), MID(Created@row, 10, 1), MID(Created@row, 10, 2))) <= 11, RIGHT(Created@row, 2) = "PM"), 1, 0)

English: If the Hour value is 4 or greater, and the hour value is 11 or lesser, and the timestamp is PM, then check this box (set the flag, star the box, etc;) otherwise, don't.

*If you really want "Yes" or "No" just replace the 1, 0) at the very end of the formula with "Yes", "No")

Jeff Reisman · Accepted Answer

Re: Business days

Look at the NETWORKDAY and NETWORKDAYS functions, as well as WORKDAY. See the Smartsheet Functions link in my signature.