Calculating hours:minutes from cells with date and time

Mommaduck

Trying to calculate total time (preferably in minutes) spent at a task/site.

Arrival and departure "times" are written in date-time format - so (arrival) [2/28/19 9:15PM] in one cell/column and (departure) [3/1/19 2:25PM] in another cell/column. 

If it helps, those cells were created by combining multiple columns - date +hour+minute+AM/PM 

Suggestions please?  Non data geek asking this question so please, explanations in non-geek greatly appreciated!

Mike Wilday

Sadly, Smartsheets is lacking a lot of ability to manage time. Functions don't exist for it. There are rumors of it being implemented at some point. But that is to be seen. 

Hopefully, someone else in the community might be able to help you. 

Paul Newcome

This is possible, but it will take some work. I haven't had to write these in a while, so it may be a few days before I get you a solution. Could you post some screenshots of your column names and the layout? I see that it can be across multiple days. Do you want the data displayed as minutes, or... 

 

Is minutes the smallest unit you will need?

Debbie Sawyer

Good luck Paul!

I wrote a formula to work out, work time on a contract which not only had to add up time elements from a Date/Time field, but also had to take into consideration different contract hours! This was in a Service Desk application called MagicTSD back in the early 2000's!  It took nearly a whole day to write and when I printed it out it was nearly a whole A4 page of nested IF's!

I did a whoop and ran round the office in joy when is parsed! This was in use then for years!

Not sure I have the will to write it again! I look forward to seeing your answer!

Kind regards

Debbie

Paul Newcome

Debbie,

 

I had one that was a very long formula that did everything, but it exceeded the 4,000 characters per cell limit that Smartsheet has. Now I use a series of helper columns to break things down and do things one part at a time. I find it much easier to work in sections and it has the added benefit of easier corrections during testing.

Mike Wilday

 Debbie! That's why I passed this one off to the rest of the community!!! 

Paul Newcome

HAHAHAHAHAHAHAHAHAHA

Paul Newcome

Mommaduck,

 

Would you be able to post some screenshots with your column names (preferably the ones you joined together) along with an example of your desired outcome?

 

I'd love to help with this, but it can get rather complicated. It makes things so much easier if I can go ahead and get those few details worked out before delving into the abyss that is time calculations in SS.

Jed Rusyniak

Paul,

I have a similar request.  Looking to calculate total dwell time in hours for trailers.   I have one column of data that has "Tender Date/Time" and what I'm looking to do is calculate "Dwell Time (in hours)" based off of current time.  I would love to work with you to see if I can solve this.   I have a very large customer that would greatly appreciate my ability to provide them with this data via Smartsheet. 

Paul Newcome

How would you need the data displayed? Are you looking to round the hours, use a decimal for them, or convert the difference to minutes? Is the smallest value an hour or minute? 

 

Smartsheet does not have a way of tracking "current time", but you can either use a manual time entry for the end point or you can use a Modified (date) type column which will include the date and time of the last time the row was changed.

mwiggins

@PaulNewcome made an excellent formula ( https://community.smartsheet.com/comment/reply/node/28066/discussion_responses/86221 ) 

The only problem is that you can't sum the Total column. 

I'm working off of his formulas to try and mass calculate total hours - put that in a column without text so you can sum it - and if you need it in days all you do is divide. 

Thoughts? 

Paul Newcome

Please note: It is true that you cannot sum the Totals column, but you can sum the helper columns as they do provide numeric values.

Ron42na

We created a time entry sheet for hourly workers...the basic approach we took was to calculate the minutes in the In/Out time cells, give errors where appropriate (missing am/pm, In minute total > Out minute total, etc.), use a checkbox column to cause conditional formatting to turn cells red if there's an error, and use another column to display the total minutes in hh:mm format. 

There are a lot of other formulas we use for things like overtime calcs, rounding time entry to 15 minute increments, weekly/monthly totals, and locking/unlocking for the approval workflow, but here are the key time formulas:

Compare the In/Out fields for obvious errors and total minute differences between the 2:

=IF(AND([Time In 1]@row = "", [Time Out 1]@row = ""), "", IF(LEN(LEFT([Time In 1]@row, FIND(":", [Time In 1]@row) - 1)) > 2, "Error", IF(LEN(MID([Time In 1]@row, FIND(":", [Time In 1]@row) + 1, 6)) > 5, "Error", IF(LEN(LEFT([Time Out 1]@row, FIND(":", [Time Out 1]@row) - 1)) > 2, "Error", IF(LEN(MID([Time Out 1]@row, FIND(":", [Time Out 1]@row) + 1, 6)) > 5, "Error", IF(AND([Time In 1]@row = "", [Time Out 1]@row <> ""), "Error", IF(AND([Time In 1]@row <> "", [Time Out 1]@row = ""), "Error", IF(AND(OR(RIGHT([Time In 1]@row, 2) = "am", RIGHT([Time In 1]@row, 2) = "pm") = true, OR(RIGHT([Time Out 1]@row, 2) = "am", RIGHT([Time Out 1]@row, 2) = "pm") = true), IF(RIGHT([Time Out 1]@row, 2) = "pm", 720 + IF(SUM(VALUE(LEFT([Time Out 1]@row, FIND(":", [Time Out 1]@row) - 1))) = 12, 0, (SUM(VALUE(LEFT([Time Out 1]@row, FIND(":", [Time Out 1]@row) - 1))))) * 60 + VALUE(MID([Time Out 1]@row, FIND(":", [Time Out 1]@row) + 1, 2)), IF(SUM(VALUE(LEFT([Time Out 1]@row, FIND(":", [Time Out 1]@row) - 1))) = 12, 0, (SUM(VALUE(LEFT([Time Out 1]@row, FIND(":", [Time Out 1]@row) - 1))) * 60 + VALUE(MID([Time Out 1]@row, FIND(":", [Time Out 1]@row) + 1, 2))))) - IF(RIGHT([Time In 1]@row, 2) = "pm", 720 + IF(SUM(VALUE(LEFT([Time In 1]@row, FIND(":", [Time In 1]@row) - 1))) = 12, 0, (SUM(VALUE(LEFT([Time In 1]@row, FIND(":", [Time In 1]@row) - 1))))) * 60 + VALUE(MID([Time In 1]@row, FIND(":", [Time In 1]@row) + 1, 2)), IF(SUM(VALUE(LEFT([Time In 1]@row, FIND(":", [Time In 1]@row) - 1))) = 12, 0, (SUM(VALUE(LEFT([Time In 1]@row, FIND(":", [Time In 1]@row) - 1))) * 60 + VALUE(MID([Time In 1]@row, FIND(":", [Time In 1]@row) + 1, 2))))), "Error"))))))))

Flip the "there's an error" check box based on the minutes total:

=IF(ISERROR([T1 Calc]229), 1, IF([T1 Calc]@row = "", "", IF(OR([T1 Calc]@row < 0, [T1 Calc]@row = "Error") = 1, 1)))

Convert a total minutes column into hh:mm format:

=INT([Rounded Work Minutes]@row / 60) + ":" + IF([Rounded Work Minutes]@row - (INT(SUM([Rounded Work Minutes]@row / 60)) * 60) = 0, "00", SUM([Rounded Work Minutes]@row - (INT(SUM([Rounded Work Minutes]@row / 60)) * 60)))

Charlie.H

Silly newbie here... but aren't there timestamps locked inside the Activity Log? Maybe Smartsheet could help us resolve multiple use case scenarios surrounding timestamps by allowing an export of Filtered Activity Logs back into a Grid...?

Andrée Starå

Hi Charlie,

Welcome to the Community and the wonderful world of Smartsheet!

Great idea! You could export the activity log and then import to a new sheet and analyze the data there or copy/move the rows to another sheet with the correct structure.

Please submit an Enhancement Request when you have a moment.

There are also rumors of a time feature coming to Smartsheet soon, and I guess that we will see it at the latest at the Engage Conference in Sep-Oct.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Katie Hart

Was there ever any resolution to this issue? I am having issues tracking time (and then being able to calculate the amount of hours/ minutes elapsed between two dates/times).

Thanks!