Converting to date and time formats

[nhbcai]

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.

 

[Robert Mika]

Hi,
maybe not the most efficent way but it does work:
B1:
B2:
B3:
B4:


C1:
C2:

D1:

 

[Robert Mika]

Hi,
Try this:
Maybe not the most efficent way but it does work:
B1:=IF(LEN(A1)=10,DATE(LEFT(A1,4),MID(A1,FIND("-",A1)+1,2),RIGHT(A1,2)),IF(LEN(A1)=4,TIME(LEFT(A1,2),RIGHT(A1,2),),IF(LEN(A1=3),TIME(LEFT(A1,1),RIGHT(A1,2),))))
copy down



C1:=B1+B2
C2:=B3+B4

D1:=LEFT(TEXT((C2-C1),"hh"),2)& " hours and "&RIGHT(TEXT(((C2-C1)*60),"hh"),2)& " minutes"

 

[T. Valko]

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.

This works for me...

A1 = 2011-04-15 as a TEXT string
A2 = 1245 as a TEXT string

To concatenate those entries and covert to a true Excel date/time:

=A1+TEXT(A2,"00\:00")

Format in the date/time format of your choice. I formatted as:

m/d/yyyy h:mm AM/PM

After you convert the other date/times then you can simply subtract the converted values to get your difference. Format that result as [h]:mm.