MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Date formulae on March 30, 2001 12:40 PM

My column A contains data like this:
Sent: Saturday, March 24, 2001 12:39 PM
Sent: Monday, March 26, 2001 7:25 AM
Sent: Friday, March 30, 2001 5:48 AM

I would like column B to show the date in mm/dd/yy format, and column C to show the time in HH:MM AM/PM format. Could someone please show me how?

Posted by mseyf on March 30, 2001 1:37 PM

assuming data in cell A4
to extract the date:

=DATEVALUE(RIGHT(A4,LEN(A4)-FIND("day, ",A4)-4))

to extract the time:

=TIMEVALUE(RIGHT(A4,LEN(A4)-FIND("day, ",A4)-4))

the use Format>Cells>Number>Date or Custom to formt to your liking