Stop defaulting to 5-digit dates

My Excel (365 64-bit) is constantly defaulting to 5-digit dates.

I already know how to change the date format of selected cells from 5-digits to a normal-looking date. But how do I stop it from defaulting to 5-digit dates in the first place? It happens under all sorts of circumstances... I'll change some data then update a table, and blam, all the dates that I had already changed to normal-looking dates have reset to 5-digit dates.

I have two colleagues who use copies of the exact same spreadsheets and pivots as me, yet their dates are always normal - their dates are never switched to 5-digit dates.

I've done piles of searches for a solution, but they all just explain how to convert the dates using Format Cells... but I don't want to have to do this 50 times/day (I'm not exaggerating). I want it to stop defaulting to 5-digit dates altogether.

Would greatly appreciate a solution!

Thanks,

Wayne

mabe change system format date to dd/mm/yyyy or whatever you want

Thanks, I'll see if that changes anything. My System Date setting was on M/d/yyyy, and now I've changed it to MM/dd/yyyy. Fingers crossed! Will let you know if it works.

Specifically what I want is to stop seeing 5 digit dates without having to change the formatting every single time. I've been using Excel on many different computers over the past 20+ years, and this is the first system I've ever used that defaults to 5-digit dates.

For example: I'm using conditional formatting to highlight cells containing dates between July 1, 2021 and June 30, 2022. So I entered those dates into the Conditional Formatting box. But now when I go back and look at my Conditional Formatting Rules, the dates are presented as 5 digit numbers. I certainly didn't write in 5-digit numbers, so Excel converted them on it's own.

Another example: I have a spreadsheet where you enter client ID #'s, refresh the table, and the client's details appear, include Start and End dates for the program they're in. I've changed the date format of the Start and End date columns to MM/DD/YYYY. But when I enter new client ID #'s and refresh the table, it gives the dates as 5-digit numbers again.

Please note that I'm not looking for specific solutions to these specific examples. I'm looking for a global solution that will stop Excel from giving me 5-digit numbers instead of dates across ALL my spreadsheets and pivots (unless of course I specifically ask it to do so).

Thanks for everyone's efforts! I fear this is a hopeless case though, as I've just had an Excel expert in my office spend time looking at it and even he can't figure it out. Maybe my Excel is corrupted.

try Styles -> right click on Normal -> Modify -> Format -> Date then choose from existing or choose appropriate Locale

I understand what you're saying about how Excel *stores* the dates (in serial form, counting the number of days since January 1, 1900). What I'm asking about is how Excel is *presenting* the dates. As you said, I don't need to see what's behind the curtain; I want to see dates in date format by default. My entire life using Excel, I see dates in date format on my spreadsheets, yet only on this computer, my dates show as serial numbers.

Here's a snip of my spreadsheet before changing the Client ID inputs:
Attachment 796449

Now here's a snip of my spreadsheet after entering new Client ID's and refreshing the table:
Attachment 796450

When my colleagues in the same office refresh the table, they see dates. Only on my computer do we get serial numbers.

This is only one example of one table. I know how to fix it by selecting the columns and manually changing the date format, but I'm trying to avoid having to change the date format every single time I refresh this table (which happens dozens of times per day). I want to make a global change so that Excel always presents dates in all my tables as dates, not as serial numbers. My colleagues' systems, and every system I've ever used in my life, default to refreshing dates to appear as dates; mine defaults to refreshing as serial numbers.

I think the reason I'm having trouble explaining this is because it's so unusual. I sit next to an Excel expert (a data analyst who programs complex spreadsheets all day and used to work for IBM) and even he can't figure out why Excel keeps switching my dates to serial format rather than a normal date format. So I think I'm just going to accept that this is an unsolvable problem (short of changing computers). But I really appreciate everyone's time trying to help!

your attachments doesn't work

Originally Posted by MrShorty

I have a lot of experience with other number formats, and I don't recall any situations where Excel just cleared number formats like you are describing.

That happens to me "all the time" with some financial functions (PV, PMT, FV).

It does not "clear" the format (I think you mean set it to General).

Instead, it changes my truly Currency format to something that Excel calls Currency, but it is a custom format that is similar to Accounting.

Unfortunately, when I try to create a simple example, Excel behaves itself. So, presumably the misbehavior depends on context.

Sadly, I cannot find an example of the misbehavior right now. It just happened to me in the last day or so. But I don't remember which Excel file I was working on. Sigh.

Can you be more specific about when it is happening please?

- You mentioned it happens when you update a table. Is this a PivotTable? I have had issues with these where you change the format of some cells but it does not persist when you refresh the table. I haven't come across it for a while but recall that it was somewhat inconsistent when edited in cell. The solution I use is to go to Field Settings and change the format of the field.

- You also mentioned conditional formatting, but I think this is normal behaviour, at least it's how I've seen it behave before. There are workarounds if you want the date to be easier to interpret.

This might not be the solution for you, but like I said, a little more detail of when it happens might help.

PS if you want to add an attachment (image or workbook), click "Go Advanced" and then "Manage Attachments", then it should work.

I've attached a simple spreadsheet demonstrating my issue.

In column A, I entered some random dates.
5-Jan-23
12-Mar-21
23-Feb-19
3-Apr-22
16-Dec-20

Then in cell G1, I entered "=A1:A5". Here's what it did:
5-Jan-23
44267
43519
44654
44181

It's easy enough for me to select the last 4 cells and convert to date format.
But I want Excel to stop presenting the numbers in serial format in the first place.
Not just in this example, but in the many other ways & places Excel is presenting my dates as serial numbers instead of normal dates.

I super-appreciate everyone's input on this, and apologize if I'm not explaining properly - most of my life, I've been a low-end Excel user; only recently have I been in a role that requires serious Excel-ing.

Thanks,

Wayne

sandy666: This solution (modifying Styles) may have worked. It worked on my simple spreadsheet... I'm going to spend some time working on my more complex sheets using this solution, and will let you know (might be early next week, as we're closed for the next 3 days).

Thank you!

In this case, I can reproduce, and it is a "feature" of dynamic arrays. Normally if you do a formula that is just equal to another cell, e.g. =A1, then Excel will try to match the formatting. But with a dynamic array, it only does it for the cell which you actually type the formula into. So in this case it does it for G1, but not for the other cells. If you type in =A1 into G1, and then either copy down or manually type =A2 into G2, and so on, then it should format correctly. Does this happen? If so, do you have other cases where it is an issue (not using dynamic arrays), or are dynamic arrays essential to your sheet?