# Working with Date/Time

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.

• ✭✭✭✭✭✭

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")

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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")

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!