Search code examples
exceldatetimedate-arithmetic

Excel date/time calculation

I'm trying to calculate the difference between two times that are given in this format:

Fri 07/02/2014 10:16 with the cell using a custom format ddd dd/mm/yyyy hh:mm

and display the result in hours.

I have two dates/times:

A1                    B1
Fri 07/02/2014 10:00  Fri 07/02/2014 10:42

The formula I have in C1 is

=TEXT(TIME(LEFT(RIGHT(B1,5),2),RIGHT(RIGHT(B1,5),2),0)-TIME(LEFT(RIGHT(A1,5),2),RIGHT(RIGHT(A1,5),2),0),"h")

Which gives me the answer 4, which I thought should be something like 0.68

Can anyone help?

Thanks

UPDATE:

A1 is actually in this format 07/02/2014 10:00:00, which is then displayed as Fri 07/02/2014 10:00

B1 is entered as 'Fri 07/02/2014 10:42' and displayed as 'Fri 07/02/2014 10:42'

Solution

  • Please try: 

    =DATE(MID(B1,11,4),MID(B1,8,2),MID(B1,5,2))+(MID(B1,16,2)+RIGHT(B1,2)/60)/24-A1

    and format the cell as [hh]:mm.