CODEWITHSUNDEEP
excelexcel-formula
Valleriani
Valleriani

Reputation: 193

How to calculate the hour between two hour times in Excel?

I have an excel sheet with the following in a row as an example: '10:00 - 16:00'.. Typically it is HOUR - HOUR2.

I am trying to get the total hours based on this. So 10:00 - 16:00 would be 6 hours. 10:00 to 16:30 will probably be 6.5 hours etc.

Is there an possible way to do this in excel with a formula?

enter image description here

I'm ok with it not doing the overly complex ones (Ones with several hour - hour2 in one column) but it would make it easier if it at least did the ones that are simplistic.

Thanks!

Upvotes: 0

Views: 48

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

With data in A1, in B1 enter:

=timevalue(RIGHT(A1,5))-timevalue(left(A1,5))

and then apply a time format to B1 to give hours:minutes. To get numeric hours, use:

=24*(TIMEVALUE(RIGHT(A1,5))-TIMEVALUE(LEFT(A1,5)))

enter image description here

Upvotes: 2

Error 1004
Error 1004

Reputation: 8220

You could try:

=TEXT(RIGHT(A1,(LEN(A1)-(FIND("-",A1)+1)))-LEFT(A1,(FIND("-",A1)-2)),"hh:mm:ss")

Results:

enter image description here

Upvotes: 0

Related Questions