Thread: [SOLVED] convert a text date into a proper date
View Single Post
  #6  
Old 01-09-2009, 06:41 PM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is offline
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
MS Office Version:2002 (work), 2003 & 2007 (home)
Posts: 6,565
daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding
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
Reply With Quote