Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
time calculation
Hi there,
I am a newby here and I must say that smartsheet looks good.
The only thing I am definetly missing is a way to calculate time
This is what I need to show:
Start End Duration h Price per hour Price total
09:00 10:30 1,5 100,00 150,00
Start and End time can be put in manually
Duration must be calculated automatically ( with a possiblity to round to the the next 10 Minutes, or quarter of a hour )
Price per hour should be a preset
Price total should be calculated automatically
Is there a way to handle this in Smartsheet?
Your help would be appriciated.
regards
Reiner
Comments

As far as I know Smartsheet doesn't calculate times very well. So maybe someone else has better knowledge than I. But here is a work around.
First Start with your Start Time and End Times and use 2 place decimals to indicate time. For example 8:30 is now 8.30 or 9:25 is 9.25.
Make two more columns ST Conversion & ET Conversion these columns will convert the time into proper decimal format.
Use the equation for ST Conversion:
=ROUND([Start Time]1) + (([Start Time]1  ROUND([Start Time]1)) * 100) * (1 / 60)Use the Equation for ET Conversion:
=ROUND([End Time]1) + (([End Time]1  ROUND([End Time]1)) * 100) * (1 / 60)In Duration use the Equation:
=IF([ET Conversion]1 > [ST Conversion]1, [ET Conversion]1  [ST Conversion]1, ([ET Conversion]1 + 12  [ST Conversion]1))This equation takes into account the possibility of going past noon or midnight to calculate the time laps. If you have jobs that go over 12 hours you will need a different equation. Or you can convert it to military time.
Once you have the duration you can easily find the total price.
One note: it may be useful if several people are using this sheet to make the start and end times controlled by a drop down menu so that it isn’t accidentally messed up by someone putting a decimal higher than 0.60.
See it working in the image below. Good Luck

Hello Reiner,
Currently we don't have a way to link tasks to time, or make calculations on time, but I'll add your vote for this capability to our enhancement request list.
Joel mentioned some great workarounds above with different ways to conver calculations into time.
Zack

Hi Reiner,
This exact problem can be solved using Azuqua, an integration partner of Smartsheet. I work for Azuqua and the logging timestamp in a column based on a checkbox being marked (checked) is one of the many scenarios that we enable inside of Smartsheet.
Using Azuqua you can set up a monitor for a change to column check for when the checkbox for "Start" has been marked and then log timestamp / date will into the column you specify.
With this solution you woud need to set up a monitor for both the start and finish checkboxes and then you would receive a duration based on the subtraction of those two values, which you can use to complete your outlined scenario above.
If you want to learn more please visit our app listing at Smartsheet here:
https://www.smartsheet.com/apps/azuqua
Please feel free to follow up with any questions.
Patrick

I am very interesdted to measure duration by time input:
Start Time: 10:00 AM
End Time: 14:26 PM
Duration: 4:26
Thanks!
Dominic

Dominic, this can be done with the following formula.
This formula looks (and is!) intimidating but its easy to use. Just paste it into a text editor and use FIND & REPLACE to replace the start and end date cell references with references from your sheet. It will require you to designate am/pm.
Here it is!
=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2))))  INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2))))  INT((((IF(LEFT(EndTime1, FIND(":", EndTime1)  1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1)  1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2)))  ((IF(LEFT(StartTime1, FIND(":", StartTime1)  1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1)  1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)
And here's an example of it in action:
https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1

Travis, this is genius.. Dont care if it works or not, it looks fantastic!

Travis,
Our event management company has been waiting so long for a way to calculate time for detailed event programs so I was really excited about your formula. However... I don't see and can't locate using the "Find" tool, any date references in your formula. What am I missing?

Randy, here are the two date cell references for the Start and End time:
StartTime1
EndTime1

Hey Travis,
Do you have a way to do that with neg hours? on a 24h clock?
StartTime = 14:00
EndTime = 10:00
Duration = 20 hours?

Hi Kelly,
Currently hourly timestamps for start and end times aren't supported, but this is on our Enhancement Request list and I've added your vote for it. Thanks for your feedback!
Kate

Add me to the list.
That October 15, 2015 solution above does not work. If I do 7.00 and 7.30 it gives me a different calculation than 8.00 and 8.30.

Ditto, we need this at our company for circa <200 PMs and looking at Smartsheets to manage Technology projects. We need to manage projects to the minute in some cases, through specfic weekends.
Here's whats missing from smartsheets and stopping our adoption and reverting to MS Project:
 Time data handling/column: and calculations to set what time a task might be done or perform time calculations.
 Non working days Exceptions: we work some weekends, after work hours, so need to manage working weekends by exception.
 No baselines: we need multiple baselines to track variance against causes.
Cheers,
Raj

Hi Travis,
I am using Patrica @ Azuqua's solution for automatically populating the start/end date/time with the ModifedAt timestamp  see https://www.youtube.com/watch?v=ouolvEfVh_A
I now need to coverts this to HH:M:SS but the example you reference above (https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1) shows an #INVALID VALUE.
Can you advise how to correct?
Many thanks
Regards
Dave

Travis,
I love the idea, though it's not working in my sheet. How must AM or PM be designated for the formula to populate correctly?
It also doesn't seem to autoupdate the formula to look at the correct row when a new row is added to the sheet.
Thanks!
KT

Hi Travis,
Not sure if you noticed this yet...I was dealing with the same issue and realized that the cell reference has no brackets. Once I added them the formula worked...example...instead of =INT((((IF(LEFT(End Time, FIND(":", End Time)  1)...use find and replace all to make start and end times look like this...=INT((((IF(LEFT([End Time]1, FIND(":", [End Time]1)  1)
Hope that helps
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives