Calculating a time before midnight and after midnight

Options

Dear Smartsheet,

I hope this message reaches you all safe and healthy.

I am trying to upgrade an existing page which is already partially formulated.

I reached a problem where we have received a call before midnight and the arrival time reached after midnight.

See below error appeared:

image.png


Under column "Sum" I have the following formula:

=((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60))

Under "SLA Standard" I have the following formula:

=INT(Sum@row) + ":" + IF((Sum@row - INT(Sum@row)) * 60 < 10, "0") + (Sum@row - INT(Sum@row)) * 60


How would I be able to get the correct calculation, in terms of time (under "SLA Standard" column), if the "E-dispatch time" is before midnight and "Actual time of arrival" is after midnight?


Many thanks in advance for your kind assistance.

Best regards,

Andi

Β«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!