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
Reiner Sackmann
Reiner Sackmann ✭✭
edited 12/09/19 in Archived 2015 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • Joel Johnson
    Joel Johnson ✭
    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

     

    time calcs.PNG

    · Share on FacebookShare on Twitter
  • Zack S
    Zack S 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

    · Share on FacebookShare on Twitter
  • Patrick Lawler
    Patrick Lawler ✭
    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.

     

    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

    · Share on FacebookShare on Twitter
  • Dominic Chan
    Dominic Chan ✭✭
    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

    · Share on FacebookShare on Twitter
  • Travis
    Travis 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

    · Share on FacebookShare on Twitter
  • Morri Young
    Morri Young ✭
    edited 03/19/16
    Options

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

    · Share on FacebookShare on Twitter
  • Randy Burtis
    Randy Burtis ✭
    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?

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 03/25/16
    Options

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

     

    StartTime1

     

    EndTime1

    · Share on FacebookShare on Twitter
  • Kelly Poirier
    Kelly Poirier ✭✭
    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?

     

    · Share on FacebookShare on Twitter
  • Kate French
    Kate French ✭
    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

    · Share on FacebookShare on Twitter
  • Shirley Hattayer
    Shirley Hattayer ✭✭✭
    Options

    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. 

    · Share on FacebookShare on Twitter
  • Raj Dhillon
    Raj Dhillon ✭
    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

    · Share on FacebookShare on Twitter
  • Dave Gordon
    Dave Gordon ✭✭✭✭
    Options

    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  

    · Share on FacebookShare on Twitter
  • KatieTMBA
    KatieTMBA ✭
    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

    · Share on FacebookShare on Twitter
  • daniel68616
    daniel68616 ✭✭✭
    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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories