Problems with date time format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 07 Jun 2006 17:49 GMT

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?

Reply to this Message

Bernard Liengme - 07 Jun 2006 18:14 GMT

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

Signature

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address

>I pull information from a computer program that my work uses, and I
> have to analyze data based on date.
[quoted text clipped - 3 lines]
> (dropping off everything after the seconds)
> Is this possible?

Reply to this Message

Ron Rosenfeld - 07 Jun 2006 18:47 GMT

>I pull information from a computer program that my work uses, and I
>have to analyze data based on date.
[quoted text clipped - 3 lines]
>(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

Reply to this Message