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

Thanks for your prompt reply.
I tried to duplicate your formula, however it return "invalid operation".
What could have gone wrong?

You have a misplaced closing parenthesis.
You should have one after the second [End Time]@row and there is one too many at the end of the formula.
=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row)  1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row  [Start Date]@row) * 24)  (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row)  1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
Answers

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.

Thanks for your prompt reply.
I tried to duplicate your formula, however it return "invalid operation".
What could have gone wrong?

You have a misplaced closing parenthesis.
You should have one after the second [End Time]@row and there is one too many at the end of the formula.
=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row)  1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row  [Start Date]@row) * 24)  (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row)  1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)

You are the best!
No wonder at the end of the formula it will automatically add another closing parenthesis.
Problem sol

Help Article Resources
Categories
Check out the Formula Handbook template!