Formulas for Calculating Time

Leslie Merlino

@Paul Newcome

Perfect!!! Thank you so much!!

Paul Newcome

@Helen Rim Here is a thread where we worked with hh:mm:ss times.

https://community.smartsheet.com/discussion/79427/calculating-time-in-hh-mm-ss

There are going to be a couple of key differences for you though. First, you only need to convert the time from one column instead of a start and end column. In the above solution there are always two digits for the hours, but I noticed in your example there is only one. So we need to take this formula:

=(VALUE(LEFT([Start Time]@row, 2)) * 3600) + (VALUE(MID([Start Time]@row, 4, 2)) * 60) + VALUE(RIGHT([Start Time]@row, 2))

And tweak it to this:

=(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) * 3600) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) * 60) + VALUE(RIGHT([Start Time]@row, 2))

Once you have all of your times converted to numerical values, you would average this column. Then to convert it back into hh:mm:ss format, you would take the three separate columns in the linked solution of "hh:", "mm:", and "ss:" and add them all together into a single string for a single cell.

=hh: formula + mm: formula + ss: formula

Paul Newcome

@Leslie Merlino Happy to help. 👍️

Helen Rim

@Paul Newcome Thank you so much! I will definitely try this out and let you know!

Syed Muhafzal

@Paul Newcome

What a thread! Came across this when I was looking for a time formula! This is truly a godsend!

Thanks so much, Paul.

I do have a question though, I am actually building a ticketing system for our IT team on Smartsheet. Our process is a ticket receives via a form and will trigger an alert etc. The issue is, we do have an SLA column that will be triaged by the IT manager based on the issue. Priority 1 will be 2H response time etc. This means, when a ticket is received at 10 am on Priority 1, we will have to respond by 12noon.

My thought on this will be

Ticket Start time: Auto Created Date (when a form is received)

Priority: Manual Entry (P1, P2, P3, etc)

SLA: Auto Cell Value based on Priority 2H, 8H, 24H

Suggested Response Time: formula based on SLA from Ticket Start time.

Completed Time : Manual Entry (when an IT person resolves the issue)

Achieved SLA : formula duration based on Suggested Response time to End time.

Of course, all SLAs have to be within office hours 8 am to 5pm. That means if I have a ticket that comes at 4:30 pm on a Monday for P1, it has to be responded to by Tuesday (the next day) at 9:30 am.....

I have seen some of your published works, and I think the Need to create a "shift" column, is probably the closest? However, I am wondering if you have other examples that may be almost to the challenge I am facing above. Even if you can point me to another thread that has a similar issue, I would really appreciate it.

Any thoughts?

Thanks

Syed

Ryan Jezierski

Hey Paul,

trying to utilize your formula for pulling the time out of the created stamp, and I'm getting an hour returned, not the minutes:

=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))

Paul Newcome

@Ryan Jezierski To include minutes, you will need to add the bold portion:

=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12)) + ":" + MID(Created@row, FIND(":", Created@row) + 1, 2)

NOTE: This formula should be outputting the time as 24 hours with a column between hours and minutes.

Razetto

Screenshot.pdf

Hello,

I'm trying to develop is a job scheduler, meaning that I have a start date and a start time for the first job (row 1) and the estimated hrs to run that particular job; what I need to calculate is the estimated completion date and the time which in turns will be the starting date and time for the following job.

It's similar to project management without using the dependencies and duration in hours. We use 24 hr format, some jobs may run for days, and the machines run 24/7.

What kind of formula can i use to calculate the completion date and completion time based on the start date, start time, and job run time? I've tried modifying many of the various formulas found throughout this post but can't make it work. Any ideas are welcome!

Theo Seale

Hi @Paul Newcome,

Hope you get this message.

Thank you for the above Highly complicated formula. I have loaded it into my spreadsheet.

The problem is that I don't want to round it off to the Hrs. I need to calculated the exacted hrs between the employee scanned in and Out.

Here is what I'm trying to create.

Each Employee will have a ID card with a QR code like below

The Project Manager will use his phone and scan in the employees and again out when they leave site.

That is the above sheet you see.

So my first Column is a Automated Date Stamp that gets created once the Employee Scans his ID Card into the Smartsheet.

Now I believe I would have to create another sheet that summarizes the Employee Name in a single row with 3 columns. Time In, Time Out and Calculation between the to to determine the amount of hours worked.

Would appreciated if you could help me here with some advise and formula's.

Thanx

Ryan Jezierski

@Paul Newcome thank you! That worked. I'm using that time, adding 1 hour to it, and hoping to use that to show operators when the next hourly check needs to be completed from a dashboard. To do that I'm trying to pull the latest check to another sheet so I can reference just the last check in a widget on the dashboard. I've tried a variety of max/collect and index/collects to do this with no luck. Any recommendation?

Paul Newcome

@Syed Muhafzal The "Need to create a shift column" solution is going to be the closest thing I have for you already put together. There is also another solution that includes how to account for lunches as well as going into a different date. You are going to want to combine pieces from all three of those.

@Razetto You are going to need to look for the solution that accounts for moving into a different date from the Start Date.

@Theo Seale Please see my last response to Ryan Jezierski to see how to include the minutes. As for combining the multiple entries for a single employee, there are a number of solutions out there for this. I believe it would most likely need to involve helper columns and INDEX/COLLECT formulas. Most of those solutions that I have seen are not necessarily time related, but there are plenty out there that should be adaptable.

@Ryan Jezierski I'm not sure I follow. Why are you trying to use the COLLECT function? Are there multiple "last checks"? Are you able to provide screenshots for reference?

Kelly Pratt

Thank you so much @Paul Newcome ! This is really helpful - although I am running into an issue and perhaps I have missed something along the way reading through this long thread :)

I am trying to do a simple calculation of hours worked (no logic needed to account for breaks or anything else). Employees will select from a drop down what time they are "clocking in" within 15 minute increments. They will then select from a drop down what time they are "clocking out"

I need to calculate the number of hours. I attempted to use the formula in the original post to convert into 24 hours as you said it is easier to calculate that way, however when I use this formula, it only puts in the hours and not the minutes that are entered from the time in sheet.

Would appreciate your input on what I am missing here! Happy to share a sheet with you if that would be helpful.

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Paul Newcome

@Kelly Pratt You are going to need something like this...

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12)) + (VALUE(RIGHT([Time Column]@row, 2)) / 60)

Kelly Pratt

Thanks @Paul Newcome ! That's not quite working, maybe I didn't explain myself correctly.

Here I've published a sheet to show an example of how I'm trying to set it up. https://app.smartsheet.com/b/publish?EQBCT=ee75840a7a44466a981e93d12187effd

Time In, Time Out is where the user would select from the drop downs.

Time In Conversion, Time Out Conversion is to convert it into a format where we can calculate the duration.

Paul Newcome

@Kelly Pratt Sorry. Didn't realize the am/pm bit. Try this instead.

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12)) + (VALUE(MID([Time Column]@row, FIND(":", [Time Column]@row) + 1, 2)) / 60)