#### 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

Options
edited 12/09/19

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?

regards

Reiner

• Options

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

• Employee
Options

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

• edited 10/26/15
Options

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.

https://www.smartsheet.com/apps/azuqua

-Patrick

• edited 03/18/16
Options

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

• Employee
Options

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

• edited 03/19/16
Options

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

• Options

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?

• Employee
edited 03/25/16
Options

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

StartTime1

EndTime1

• Options

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?

• edited 06/09/16
Options

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

• Options

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.

• Options

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

• ✭✭✭✭
Options

Hi Travis,

I am using Patrica @ Azuqua's solution for automatically populating the start/end date/time with the ModifedAt timestamp - see

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

• Options

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

• ✭✭✭
Options

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

This discussion has been closed.