Dates as Text instead of Number?

[Ashkaan]

Can anyone explain to me what's going on here and how I can fix it?

My goal is to see the date as MM/DD/YY, not NNNNN.

\1

[alansidman]

Excel stores dates a numerical value with Jan 1, 1900 as 1. Click on the range of dates and set the format to date and select the date format you wish. Date formatting is on the Home tab of the ribbon.

[Tony Valko]

Sometimes this will work...

Select the cells in question
Goto the Data tab>Text to columns
Click Next twice
In step 3 of the userform under Column data format, select Date
In the drop down select MDY
Click Finish

[Ashkaan]

Click on the range of dates and set the format to date and select the date format you wish. Date formatting is on the Home tab of the ribbon.

Tried that. That's why it says Date at the top in the ribbon.

[Ashkaan]

Select the cells in question
Goto the Data tab>Text to columns
Click Next twice
In step 3 of the userform under Column data format, select Date
In the drop down select MDY
Click Finish

That looked promising, but it didn't do it. Thanks, though.

[alansidman]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome (manually entered is ok) and how you arrived at that. (Exclude sensitive info). Pictures are pretty much impossible to edit.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Tony Valko]

In your screencap the numbers (date serial numbers) are aligned left. This is an indication that the numbers are formatted as TEXT.

Try this...

Select an unused empty cell that has not been formatted in any way.

Copy that cell: Right click>Copy
Select the range of date cells
Right click>Paste special>Add>OK

If the date numbers are now aligned right then they have been converted from TEXT numbers to numeric numbers.

Then you can format these cells in the date format of your choice.

[Ashkaan]

Select an unused empty cell that has not been formatted in any way.

Copy that cell: Right click>Copy
Select the range of date cells
Right click>Paste special>Add>OK

Didn't work.

[Ashkaan]

upload a sample of your workbook

I can't because there is a lot of sensitive data about celebrities in here. I'll try to copy out the problem area and post a new file with just that.

[Ashkaan]

I'll try to copy out the problem area and post a new file with just that.

Ok, when I copied the cells out of that workbook into another, it suddenly looked perfect. What could be effecting my sheet like this?

[alansidman]

I can't because there is a lot of sensitive data about celebrities in here.

Just dummy up the confidential data. We don't need the entire file, but only a representative sample.

[Ashkaan]

I appreciate the effort, everyone. I just ended up copying the entire sheet and pasting into a new sheet. The dates corrected and my conditional formatting came along for the ride too. I'm happy. Thanks anyway.

[Tony Valko]

Good deal. Thanks for the feedback!