Calculate time

Joseph Farahat

Hi Everyone,

years ago, I was told that smartsheet does not calculate time.

Has the ability to calculate time been added?

It would be nice to move this task over to smartsheet from Excel.

I am looking to get the results in the blue column.

Joseph Farahat

Hi Jeff,

Thank you SO!! much. This is beyond the simple answer i expected.

It is odd that Smart-sheet cannot add this. Seems like it would be used by many off site worker to log in hours of work.

I got puled away for work and will give it a shot this weekned in a quite space.

Have a great weekend.

Joseph.

Jeff Reisman

@Joseph Farahat

No, time calculations have not been added to Smartsheet. It is still a giant pain to do what you want. However, I did just implement something similar this week involving hours, net days, etc.

First I would recommend going to 24-hour time. 1pm = 13:00, etc., without AM / PM. Then, get ready for a ton of nested IFs, helper columns, and converting text to values in order to do the math, and then putting them back together as strings.

To calculate your Leave Warehouse time:

Lets call the first column DockTime, the second column TravelTime. Add a helper column called DockHour. For DockHour:

=IF(LEFT(DockTime@row, 2) <> ":", VALUE(LEFT(DockTime@row, 2)), VALUE(LEFT(DockTime@row, 1)))

Add a helper column called DockMinute. For DockMinute:

=IF(RIGHT(DockTime@row, 2) = "00", 60, VALUE(RIGHT(DockTime@row, 2)))

TravelHour helper column:

=IF(LEFT(TravelTime@row, 2) <> ".", VALUE(LEFT(TravelTime@row, 2)), VALUE(LEFT(TravelTime@row, 1)))

TravelMinute helper column:

=IF(RIGHT(TravelTime@row, 2) = "00", 60, VALUE(RIGHT(TravelTime@row, 2)))

For the Leave warehouse time column:

=IF(OR(TravelMinute@row > 0, TravelMinute@row < DockMinute@row), (DockHour@row - TravelHour@row), (DockHour@row + 1 - TravelHour@row)) + ":" + IF(AND(DockMinute@row = 60, TravelMinute = 60, "00", IF(DockMinute@row > TravelMinute@row, (DockMinute@row - TravelMinute@row), (DockMinute@row + TravelMinute@row))

Now I'm not making any guarantees, but if you switch to 24-hour time with no AM/PM, and travel time as H.QTR HR, you should get the correct 24-hour time HH:MM to leave the warehouse.

Do the same thing with the Hours Worked:

StartHour helper column:

=IF(LEFT(StartWork@row, 2) <> ":", VALUE(LEFT(StartWork@row, 2)), VALUE(LEFT(StartWork@row, 1)))

StartMinute helper column:

=IF(RIGHT(StartWork@row, 2) = "00", 60, VALUE(RIGHT(StartWork@row, 2)))

EndHour helper column:

=IF(LEFT(EndWork@row, 2) <> ":", VALUE(LEFT(EndWork@row, 2)), VALUE(LEFT(EndWork@row, 1)))

EndMinute helper column:

=IF(RIGHT(EndWork@row, 2) = "00", 60, VALUE(RIGHT(EndWork@row, 2)))

Total Hours:

=IF(AND(StartHour@row < EndHour@row, OR(EndMinute@row > 0, EndMinute@row < StartMinute@row)), (StartHour@row - EndHour@row), IF(AND(StartHour@row > EndHour@row, OR(EndMinute@row > 0, EndMinute@row < StartMinute@row)), (StartHour@row + 1 - EndHour@row)) + ":" + IF(AND(StartMinute@row = 60, EndMinute@row = 60, "00", IF(StartMinute@row > EndMinute@row, (StartMinute@row - EndMinute@row), (StartMinute@row + EndMinute@row))

HAVE FUN!! 😀

Jeff Reisman

I quickly tested this out this morning.

First, TravelTime needs to be stored as text. So to get 1.30, you'd need to enter it as '1.30

DockHour:

=IF(MID(DockTime@row, 2, 1) <> ":", VALUE(LEFT(DockTime@row, 2)), VALUE(LEFT(DockTime@row, 1)))

TravelHour:

=IF(MID(TravelTime@row, 2, 1) <> ".", VALUE(LEFT(TravelTime@row, 2)), VALUE(LEFT(TravelTime@row, 1)))

In the Warehouse leave time formula, I had a few errors (a misplaced parentheses and a missing @row. Fixed:

=IF(OR(TravelMinute@row > 0, TravelMinute@row < DockMinute@row), (DockHour@row - TravelHour@row), (DockHour@row + 1 - TravelHour@row)) + ":" + IF(AND(DockMinute@row = 60, TravelMinute@row = 60), "00", IF(DockMinute@row > TravelMinute@row, (DockMinute@row - TravelMinute@row), (DockMinute@row + TravelMinute@row)))

StartHour:

=IF(MID(StartWork@row, 2, 1) <> ":", VALUE(LEFT(StartWork@row, 2)), VALUE(LEFT(StartWork@row, 1)))

EndHour:

=IF(MID(EndWork@row, 2, 1) <> ":", VALUE(LEFT(EndWork@row, 2)), VALUE(LEFT(EndWork@row, 1)))

The TOTAL HOURS formula needs work. I'll get back to you.

Joseph Farahat

Hi Jeff,

Thank you SO!! much. This is beyond the simple answer i expected.

It is odd that Smart-sheet cannot add this. Seems like it would be used by many off site worker to log in hours of work.

I got puled away for work and will give it a shot this weekned in a quite space.

Have a great weekend.

Joseph.

tim_sims23

Did Jeff ever post his corrections to the total hours formula? I am looking to use this for my sheet.