time intervals

[KC011]

Hello learned folk.
I have been given large files with a consistent format for the date/time of an observation as follows: " 08.03.2008, 23:00:00.000". There are thousands of date/times in a single column and what I want to do is calculate the time interval between successive observations. As it happens the second observation in the next row is "09.03.2008, 04:00:00.000" so the calculated time in hh:mm ought to be 05:00.
Issues - the format of the cell doesn't seem to be a date/time and I don't know how to convert. When I check format it is just "general" rather than date. I'm sure I can use formula to calculate the time interval if I can do a mass conversion of the date/time format from the nonsense it is into something - which it should be : dd/mm/yyyy hh:mm ! Any thoughts (btw there are many thousands of rows of dates and times and I need time intervals between all successive time/dates...?

Cheers

 

[barry houdini]

Hello KC011, welcome to MrExcel

To convert with a formula try this formula in B2 for a date/time in A2

=REPLACE(REPLACE(REPLACE(A2,3,1,"/"),6,1,"/"),11,1,"")+0

Format as required - e.g. dd/mm/yyyy hh:mm:ss.000 - then copy down column

 

[joeu2004]

Select the range, press ctrl+H (Find and Replace), set Find What to period ( . ) and Replace With to slash ( / ), then click Replace All.

Format the range as Custom dd/mm/yyyy hh:mm:ss.000 .

 

[barry houdini]

Select the range, press ctrl+H (Find and Replace), set Find What to period ( . ) and Replace With to slash ( / ), then click Replace All.

Format the range as Custom dd/mm/yyyy hh:mm:ss.000 .

but won't that change 08.03.2008, 23:00:00.000 to 08/03/2008, 23:00:00/000?

 

[joeu2004]

but won't that change 08.03.2008, 23:00:00.000 to 08/03/2008, 23:00:00/000? [embellished]

Yes. My bad*2.