Calculating time elapsed date and time

Liz Lomax

Hi all,

I've read a few solutions on how to do this but just can't get my head around to apply it to my own sheet.

I have an incident register that we are moving into smartsheet and one of the columns is duration of outage.

In excel we have two columns; Incident start date + time and Incident end date + time (format dd/mm/yyyy hh:mm) and we just subtract one from the other , and then *1440 to get the result in minutes.

This date is then used in a dashboard showing total amount of outage time each period.

In the smartsheet version we have already broken the dates and times out into separate columns, as the time just seems to vanish if you input it into a date-formatted column.

And obviously subtracting one date from another is no issue. But I just can't get my head around how to subtract end date AND time from start date AND time in smartsheet. (but also a giant WTF @smartsheet?! Why isn't this functionality built already??!)

Because the data in this sheet will be fed directly in from sharepoint forms (not my project, all configuration out of my control), I am not sure if using helper columns is an option or not, so I was wondering if a helper sheet was an option instead?

Can anyone point me in the right direction please?

TIA and happy hols!

Paul Newcome

Here is a thread with a lot of time based calculations including date/time differences. I think it is up to 9 pages now, but I am sure there are a few different solutions there that you should be able to use.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time