Date not acting as a date

[bobgrand]

Hello all,

I receive a spreadsheet from an outside source and the date column is not actually formated as a date. It looks like a date but does not act as a date. The format of this column is General and the date is shown as 12/31/99. All the raw data sits in Sheet 1 and Sheet 2 extracts most of those columns data from sheet 1. The format for this date in sheet 2 is DATE MM/DD/YY but for some reason it's not seeing the 99 as 2099.

Is there something I can add to the formula so it acts as a real date.

 

[Joe4]

If the date was entered as a date as 12/31/99, it will assume 12/31/1999, not 12/31/2099.

If you change the format of the cell to mm/dd/yyyy, what does it show?
If it does not show a 4 digit year, then it is entered as Text, and not as a Date.

 

[Trevor G]

What happens if you toggle the sheet to display the contents does it show the dates as numbers within the cells?

 

[PeteWright]

Hm, just tried out something and with entering "12/31/99" into a cell formatted as "General" I get "12/31/1999".
The earliest possible year is 1900, so maybe Excel formats "99" as "1999".
Dates actually being text, that mostly happens when pasting or importing data while actually typing in a date should work, but that does't solve your problem.

What you could do is copying and re-pasting all data with one extra step.
1. Just copy all cells with data and paste them into a text editor (preferably Notepad++ which is free)
2. Then again copy everything and go back to Excel and use the "Text Import Wizard"

This should "convert" all your data to actual Dates.

If you need further assistance, just let me know.

 

[MARK858]

Please see the link below, in particular the 2029 rule, which is the default (please also pay attention to the note immediately above the 2029 rule)

Excel works with two-digit year numbers - Microsoft 365 Apps

Describes how Microsoft Excel determines the century when you type a date using a two-digit year number.

learn.microsoft.com

 

[bobgrand]

Joe4 - After changing the format to mm/dd/yyyy it did nothing. it remained 12/31/99.

Trevor G - "toggle the sheet to display the contents" << I never heard this phrase before can you elaborate?

PeteWrite - The Notepad++ was another step I was thinking about but trying not to do any extra steps. I hate moving around raw data from one place to another I don't want it to get compromised in any way. But if it comes down to it I may just take this path.

MARK858 - Thank you, I was unaware of this rule.

Thanks for the input but I did figure out a formula to help the issue.

Have a great day

 

[MARK858]

MARK858 - Thank you, I was unaware of this rule.

You're welcome (btw I suspect Trevor just meant format the cells as General, if they are real dates they would change to a number [today would be 45666], if the cell contained text looking like a date it would still look like the date).

Happy you have found a workaround

 

[Trevor G]

As Mark has indicated. There is a keyboard option to switch from what you see on the sheet. One method in Excel to use the toggle switch, use the keyboard combination of the CTRL key plus the Accent key which is the keyboard symbol just below the Escape key which shows 3 symbols one of which is the Accent key `. So CTRL + ` together will change the view of any date cell into its number and use it again it will switch it back to the normal view. So if when toggle open it just remains as a date it will indicate the cell content is text rather than a number.

 

[MARK858]

@Trevor G you should point out that the Ctrl + Accent only gives the date serial if the date is typed in the cell, if the date is via a formula it will toggle between the formula (rather than the date serial) and the date.