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
Last edited by neowok; 01-09-2009 at 07:48 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
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
Last edited by neowok; 01-09-2009 at 07:15 PM.
try
=VALUE(A1)
edit oops to quick sorry
that returns 1 jan 05
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
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
thanks that worked
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks