Problems with date time format

[Jay]

I pull information from a computer program that my work uses, and I
have to analyze data based on date.
The program exports the date as 6/30/2005 09:46:16:296, which excel
does not recognize as a date/ time format. I need a macro that
converts any date/ time like the above to: 6/30/2005 09:46:16
(dropping off everything after the seconds)
Is this possible?

 

[Bernard Liengme]

Just removing the time will not help. You are importing text; Excel stores
dates as serial numbers (starting with 1 Jan 1900) and DISPLAY this number
as a date. You will need to use text functions such as LEFT, MID and RIGHT
to extract month, day and year and use the YEAR function to make a valid
serial date.
Sitting in an airport right now so I have no time to experiment for you.
best wishes

 

[Ron Rosenfeld]

I pull information from a computer program that my work uses, and I
have to analyze data based on date.
The program exports the date as 6/30/2005 09:46:16:296, which excel
does not recognize as a date/ time format. I need a macro that
converts any date/ time like the above to: 6/30/2005 09:46:16
(dropping off everything after the seconds)
Is this possible?

If your Windows Regional Settings are MDY, then you can use the formula:

=--SUBSTITUTE(A1,":",".",3)

will convert the above to time INCLUDING the fractional seconds.

If you don't want the fractional seconds, then use:

=--LEFT(SUBSTITUTE(A1,":","~",3),FIND("~",SUBSTITUTE(A1,":","~",3))-1)




--ron