Excel 2007 : Converting to date and time formats

I have exported some data from Crystal Reports which includes date and time fields. Unfortunately the data shows as two seperate fields YYYY-MM-DD and the time as HHMM.
So, I have a start date/time like
(cell A1) 2011-04-15 (cell A2) 1245
and an end date and time like
(cell A3) 2011-04-16 (cell A4) 0700
I want to calculate the time difference between these two dates/times and presumably need firstly to combine the start and end date /time entries respectively but of course excel isn't recognising these fields as genuine date and time fields and so any concatination doesn't give a true date/time field.
Two question therefore.
1 - How do I convert these fields so they are genuine date and time fields and that they can then be combined.
2 - How do I calculate the time difference in hr and mins

Many thanks in advance of a solution.

Try:

=(DATEVALUE(A3)+TIMEVALUE(LEFT(B3,2)&":"&RIGHT(B3,2)))-(DATEVALUE(A1)+TIMEVALUE(LEFT(B1,2)&":"&RIGHT(B1,2)))

Custom formatted as [h]:mm

Dom

FWIW, you might find you could use

Please Login or Register  to view this content.