Divide DateTime to Date and Time

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hello,

I have a text file with several columns one of which represents dates
in the following format:

dd mmm, yyyy hh:mm:ss AMPM

For example:

03 July, 2008 2:13:16 PM
11 August, 1976 11:03:50 AM

I need to get the date and time portions in different columns. I have
tried datevalue, timevalue, text, etc. but still cannot divide the two
portions. I would like to do this without any macro or VB/VBA code.

Is it possible? I am using Excel 2007

Thanks
 
Hello,

I have a text file with several columns one of which represents dates
in the following format:

dd mmm, yyyy hh:mm:ss AMPM

For example:

03 July, 2008 2:13:16 PM
11 August, 1976 11:03:50 AM

I need to get the date and time portions in different columns. I have
tried datevalue, timevalue, text, etc. but still cannot divide the two
portions. I would like to do this without any macro or VB/VBA code.

Is it possible? I am using Excel 2007
Click to expand...

Yes, but how will depend on what is actually in the cell you are referring
to.
If it's a date (which XL stores as a number) you can simply change the
formatting to show only the date or only the time.
If it's a text string (as seems to be the case here), then you can simply
use the text functions (left, right, mid, len etc.) to pull it apart into
the bits you want.

HTH
 
If it's a text string (as seems to be the case here), then you can simply
use the text functions (left, right, mid, len etc.) to pull it apart into
the bits you want.
Click to expand...

Precisely, I want to know how to do that. The positions of the
different "bits" are not fixed (different months have different
lengths, the hours are not left padded with zeroes, etc.), that's the
problem! :)
 
dates in the following format:
dd mmm, yyyy hh:mm:ss AMPM
For example:
03 July, 2008 2:13:16 PM
11 August, 1976 11:03:50 AM
Click to expand...

To extract the date as a DATE:

=--SUBSTITUTE(LEFT(A1,FIND(",",A1)+5),",","")

Format as DATE

To extract the time as a TIME:

=--MID(A1,FIND(",",A1)+7,20)

Format as TIME
 
Give these a try (assuming your "date/time" value is in A1)...

Date Portion: =--SUBSTITUTE(SUBSTITUTE(A1,RIGHT(A1,11),""),",","")

Time Portion: =--TRIM(RIGHT(A1,11))

Rick
 
Back
Top