exported date field not recognized as date in Excel...Help!

[sawgee2001]

When I export a date field from my payroll database into Excel, Excel does not recognize the data as a "date". For example, January 15, 1976 would be entered into the our payroll software as 01/15/1976, and when this field is exported into Excel, it appears as 01/15/1976. However, when I try to sort on the column of exported data to create a list based on seniority, it sorts based on the data from the left, not by the year. I've checked the regional settings, and it is set as "mm/dd/yyyy". I've tried to format the column, using a custom date format, but this does not seem to change anything. Any ideas out there?
This message was edited by sawgee2001 on 2002-11-27 00:06

 

[Aladin Akyurek]

Excellent Aladdin, in fact since you resolved this puzzle i have opened an account here for more info regarding Excel (I had the same problem for several years!). THANKS!

That's great. Thanks for providing acu a nice feedback.

 

[Ravin]

Hi Aladin,

I have the same issue and have seen you thread what is meant by


these two steps (3.) Activate Edit|Paste Special >Add. (i.e What is >ADD?)

(4.) Format the date range as date.[/QUOTE]


What is .[/QUOTE]

Rav



Let A1 house an imported date.

If

=ISNUMBER(A1)

results in FALSE, do the following...

(1.) Copy an empty cell.
(2.) Select the range of imported dates.
(3.) Activate Edit|Paste Special >Add.
(4.) Format the date range as date.[/QUOTE]

 

[Aladin Akyurek]

Hi Aladin,

I have the same issue and have seen you thread what is meant by


these two steps (3.) Activate Edit|Paste Special >Add. (i.e What is >ADD?)

(4.) Format the date range as date.

What is .[/QUOTE]

Rav



Let A1 house an imported date.

If

=ISNUMBER(A1)

results in FALSE, do the following...

(1.) Copy an empty cell.
(2.) Select the range of imported dates.
(3.) Activate Edit|Paste Special >Add.
(4.) Format the date range as date.[/QUOTE][/QUOTE]

The recipe I posted consists of:
(1.) Copy an empty cell.
(2.) Select the range of imported dates.
(3.) Activate Edit|Paste Special >Add.
(4.) Format the date range as date.

The foregoing has no "QUOTE" at the end. It's by the way an html tag.
If you are at the Paste Special step, you'll see Add an option to show.

Hope the foregoing clarification helps.

 

[Ravin]

What is .

Rav



Let A1 house an imported date.

If

=ISNUMBER(A1)

results in FALSE, do the following...

(1.) Copy an empty cell.
(2.) Select the range of imported dates.
(3.) Activate Edit|Paste Special >Add.
(4.) Format the date range as date.[/QUOTE][/QUOTE]

The recipe I posted consists of:
(1.) Copy an empty cell.
(2.) Select the range of imported dates.
(3.) Activate Edit|Paste Special >Add.
(4.) Format the date range as date.

The foregoing has no "QUOTE" at the end. It's by the way an html tag.
If you are at the Paste Special step, you'll see Add an option to show.

Hope the foregoing clarification helps.[/QUOTE]

hi I am still confused I get to false results

I copy a empty cell select range of bad dates right click paste special no option to add

 

[Aladin Akyurek]

...

hi I am still confused I get to false results

I copy a empty cell select range of bad dates right click paste special no option to add

How to Use Paste Special in Excel 2010 - For Dummies

This contains a picture of Paste Special dialog.

 

[JimN235]

Excellent Aladdin, in fact since you resolved this puzzle i have opened an account here for more info regarding Excel (I had the same problem for several years!). THANKS!

I have had this problem and found other work arounds that "worked" but this is simple and fast. THANK YOU!

 

[Aladin Akyurek]

I have had this problem and found other work arounds that "worked" but this is simple and fast. THANK YOU!

You are welcome. Thanks for providing feedback.

 

[jambon2]

Hi this didn't seem to work for me:

Copied blank cell
pasted special add into cell with spurious date
changed cell format to date format

but no change?

Thanks

 

[Aladin Akyurek]

Hi this didn't seem to work for me:

Copied blank cell
pasted special add into cell with spurious date
changed cell format to date format

but no change?

Thanks

You mean the recipe discussed in this thread did not help to transform your date cell into a true date housing cell, right?

Let's try another recipe:

Select the cell.
Activate the Data | Text to Columns.
At the 3rd step, choose a date format for Date.
Click Finish.

Does this help?