time calculation

Reiner Sackmann

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

Joel Johnson

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

 

Zack S

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

Patrick Lawler

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

Dominic Chan

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

Travis

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

Morri Young

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

Randy Burtis

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?

Travis

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

 

StartTime1

 

EndTime1

Kelly Poirier

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?

 

Kate French

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

Shirley Hattayer

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. 

Raj Dhillon

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

Dave Gordon

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&nbsp;

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  

KatieTMBA

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 auto-update the formula to look at the correct row when a new row is added to the sheet. 

 

Thanks!

KT

daniel68616

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