I need to have function to put time on smartsheet

Barzan Hassan

I am creating a tracker and I need to have date and time either together or alone, can I do that?

I couldn't find the function of time?

Jeff Reisman

@Barzan Hassan Unfortunately there are no built in functions for time. Smartsheet is notoriously bad at time, even with the date/time system fields ("Created" and "Last Modified:"), because those times are actually the time in the time zone where the server you are connected to resides, but the time you see on-screen changes to match the time zone set in your regional settings.

The only way to handle time is manual entry of time values, along with using basic math functions against the values of portions of the manually entered time values. It can be done, but it is messy and not easy to get right.

Some tips for if you decide to try this:

Use 24-hour time vs AM/PM. Trust me, the math is way easier, far fewer variables.

Standardize the format to be four characters, always. 6:30 AM should be 0630, 4:30 PM should be 1630, etc. This allows you to obtain the hour portion for calculations by using something like =VALUE(LEFT(StartTime@row, 2)) for any time value. With this formula, if your start time is 0715, you get a numeric hour value of 7, and if the end time is 1545, you get a numeric hour value of 15. You can do the same thing with the minute portion, using =VALUE(RIGHT(StartTime@row, 2)).

Calculating minutes can be tricky. If the minute value of the EndTime is greater than the minute value of the StartTime, no problem: subtract the Start hour value from the End hour value, and the Start minute value from the End minute value. For example: a start time of 0730 and end time of 1445 equals 7 hours 15 minutes.

However, if the minute portion of the EndTime is less than the minute portion of the StartTime, you need to subtract 1 from the hour calculation. For example: start time is 0745 and end time is 1415. The total hours isn't 14-7 = 7, because you didn't go a full 7 hours. So you subtract one from the hours to get 6.

For the minutes, this is harder. You need to figure out how many minutes remained in the start hour, and add them to the total minutes elapsed in the end hour. From the example above, you would do (60-VALUE(RIGHT(StartTime@row, 2))) + VALUE(RIGHT(EndTime@row, 2)) which results in (60-45) + 15 = 30. Total time then is 6 hours 30 minutes. If your times were 0745 and 1420, your minutes result would be (60-45) + 20 = 35, for total time of 6 hours 35 minutes.

Good luck!!