[SOLVED] convert a text date into a proper date

neowok · #1 01-09-2009, 06:52 PM

I have a column of dates in thie format " January 5 03:09:36 2009" which i need to convert into a proper excel date that i can do further calculations on (adding up things, graphs etc based on dates).

Note the space before the month name. I thought about doing a left(cell,xx) to get it but that isnt going to work with the space infront and the variable length of month names.

I do not necessarily need the time included, it may be useful at some point if its easy to keep it as part of the data, if not i can live without it.

Thanks

**daddylonglegs** · #2 01-09-2009, 07:04 PM

Are you in the US? If your default date format is m/d/yy then, assuming your timestamp in A1 you can extract the date with this formula in B1

=MID(REPLACE(A1,LEN(A1)-13,9,","),2,99)+0

Format B1 as a date

If you want to include the time, too try

=MID(REPLACE(A1,LEN(A1)-13,0,", "&RIGHT(A1,4)),2,LEN(A1))+0

and format as date and time

neowok · #3 01-09-2009, 07:11 PM

Thanks

I am in UK so d/m/y

tried those anyway but got #value error, it didnt seem to like the +0 at the end, taking it off seems to show the date but leaves it as text still

martindwilson · #4 01-09-2009, 07:28 PM

try
=VALUE(A1)
edit oops to quick sorry
that returns 1 jan 05

neowok · #5 01-09-2009, 07:30 PM

nup dont work, comes up as 1st jan 2005

i think the solution above will work, i just need to figure out how to move the day to the left of the month in the formula to UK format and then i guess itll work

**daddylonglegs** · #6 01-09-2009, 07:41 PM

For UK settings try like this for the date

=REPLACE(REPLACE(A1,FIND(":",A1)-2,9,MID(A1,2,FIND(" ",A1,2)-1)),1,FIND(" ",A1,2),"")+0

and for date and time

=REPLACE(REPLACE(A1,FIND(":",A1)-2,9,MID(A1,2,FIND(" ",A1,2)-1)),1,FIND(" ",A1,2),"")+LEFT(RIGHT(A1,13),8)

If you want date in one cell annd time in another then use the first one for the date and

=LEFT(RIGHT(A1,13),8)+0

for the time

neowok · #7 01-09-2009, 07:45 PM

thanks that worked

Welcome to the Excel Forum

Forum Statistics