Working with Date/Time

C.M.

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

@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

Re: Business days

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

Jeff Reisman

@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

Re: Business days

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

C.M.

@Jeff Reisman that was very helpful and worked just fine. Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327655#Comment_327655

Happy to help! I also figured some stuff out here that I can use on a sheet for our logistics dept., so win-win.