Excel date/time calculation

1
votes

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'

3
exceldatetimedate-arithmetic
Yes, it gives a #VALUE! error – Jazz
@pnuts Please see update. These values are being pasted in from another source, so I need to try and work with the pasted data. – Jazz

3 Answers

1
votes

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.

1
votes

It should be sufficient to remove the day part from the string in B1, assuming that your default date format is dd/mm/yyyy then this should work in C1

=MID(B1,5,16)-A1

format C1 as [h]:mm

-1
votes

Dates and time are just numeric values, integer part represent date (number of days since 1900-01-01) and fractional part represents time. So as he have 24 hours in a day, and if you have valid time values, independent of the format, hours can be calculated as:

= (B1-A1) * 24