Divide DateTime to Date and Time

[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

 

[Bruce Sinclair]

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

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

 

[Mark]

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.

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!

 

[T. Valko]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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