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:

StartTime1

EndTime1


=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?

Thanks.

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?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Vivien Chong


    Hey there. I recently helped with a similar solution. Take a look at THIS THREAD and feel free to let me know if you need help adapting it to suit your needs.

    thinkspi.com

  • 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?

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Paul Newcome

    You are the best!

    No wonder at the end of the formula it will automatically add another closing parenthesis.

    Problem sol

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    thinkspi.com