Date Formats

[GorD]

How can I format these dates to enable me to do calculations on them - basic subtraction, networkdays etc etc.

Dates are coming from another system - thousands of lines

START_TIME_STAMP COMPLETE_TIME_STAMP
Feb 24, 2011 7:46:43 AM Feb 24, 2011 7:51:00 AM
Mar 22, 2011 10:50:43 PM Mar 23, 2011 12:49:08 AM



Many thanks for any help

 

[Jonmo1]

You might try

Data - Text to Columns
Deliminated - Next
Next
Choose Date - with an appropriate format MDY or DMY etc..
Finish

For the step choosing the date format, choose a format according to how your dates are CURRENTLY displayed, not how you want the result to be displayed..


Hope that helps.

 

[GorD]

Hi Jon, thanks for trying

I have used the text to columns frequently in the past with dates, but can't get it to help here.
I have tried changing format to

mmm/dd/yyyy h:mm AM/PM, but still can't do any calcs

 

[Jonmo1]

Text to columns - Date - MDY

That worked for me on your sample set
It even preserved the times...

It's difficult to tell from your sample, but is that only 2 columns?

Like

Excel Workbook
	A	B
2	Before Text to columns MDY
3
4	START_TIME_STAMP	COMPLETE_TIME_STAMP
5	Feb 24, 2011 7:46:43 AM	Feb 24, 2011 7:51:00 AM
6	Feb 24, 2011 7:46:43 AM	Mar 23, 2011 12:49:08 AM
Sheet1

Excel Workbook
	D	E	F
2	After Text to columns MDY
3
4	START_TIME_STAMP	COMPLETE_TIME_STAMP	Formulas
5	2/24/2011 7:46	2/24/2011 7:51	0:04:17
6	2/24/2011 7:46	3/23/2011 0:49	641:02:25
Sheet1

 

[GorD]

Still having issues with this , these are two fields from around 32 columns,
Might have to un pick and reassemble, but was hoping to avoid if possible

can't get any format that i can calculate on

START_TIME_STAMP COMPLETE_TIME_STAMP
Feb 24, 2011 7:46:43 AM Feb 24, 2011 7:51:00 AM
Mar 14, 2011 8:44:14 AM Mar 14, 2011 10:57:08 AM
Mar 8, 2011 10:32:32 PM Mar 9, 2011 1:00:25 AM
Mar 9, 2011 3:07:46 PM Mar 9, 2011 4:31:43 PM
Mar 9, 2011 10:40:32 PM Mar 10, 2011 12:51:47 AM
Mar 9, 2011 10:00:01 PM Mar 10, 2011 12:42:44 AM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
Feb 24, 2011 1:27:51 PM Feb 24, 2011 5:05:43 PM
Feb 24, 2011 1:27:51 PM Feb 24, 2011 5:05:43 PM
Feb 11, 2011 11:28:32 AM Feb 27, 2011 1:25:50 PM
Feb 23, 2011 6:11:16 PM Feb 23, 2011 7:23:54 PM
Feb 27, 2011 1:24:59 PM Feb 27, 2011 1:25:50 PM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
May 18, 2011 11:52:05 AM May 18, 2011 3:30:10 PM
Jan 8, 2011 12:49:18 PM Jan 8, 2011 1:31:33 PM
Feb 21, 2011 10:32:33 PM Feb 22, 2011 12:41:31 AM
Feb 21, 2011 10:32:33 PM Feb 22, 2011 12:41:31 AM
Mar 29, 2011 10:02:28 PM Mar 29, 2011 11:50:33 PM
Mar 3, 2011 12:49:29 PM Mar 3, 2011 12:49:46 PM

Should text to columns sort the ones with single digit date entry?

 

[Jonmo1]

Yes it should...

Make sure:
1. Text to columns can only do 1 column at a time
So don't highlight both columns when doing text to columns..

2. Only highlight the date fields, NOT the headers (I had trouble with that too).


If this still doesn't work, use Excel Jeanie to post your data..See my signature..