Formula to calculate time

Earlier I found the following formula in this forum to calculate time, as follows:

Let's say it is row 1 and the columns are:



=VALUE(LEFT(EndTime1, (FIND(":", EndTime1) - 1))) - VALUE(LEFT(StartTime1, (FIND(":", StartTime1) - 1))) + (VALUE(RIGHT(EndTime1, (FIND(":", EndTime1) - 1))) - VALUE(RIGHT(StartTime1, (FIND(":", StartTime1) - 1)))) / 60

However, I realised that for time before 8:00, it couldn't count accurately. (Refer to picture below, highlighted in red)

Anyone has more accurate formula to calculate time?


Best Answers

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Answer ✓

    HI @Paul Newcome

    Thanks for your prompt reply.

    I tried to duplicate your formula, however it return "invalid operation".

    What could have gone wrong?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!