Dates show DMY in cell, but MDY in formula bar - won't sort or convert

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
89
Hello -

I'm using O365 in the US but have system time on my PC set for Australia (in case this matters…)

WorkSheet #1

I have received two excel worksheets, one has dates formatted in DMY, and if I switch the formatting of any of those cells to General, I see that the resulting number (in General format) is a date serial number.

When I convert this entry: 6/01/2016 to General, I get the serial number 42375. This makes me believe that this cell is in a valid Date format, (and thus will eventually be sortable by date, which is what we are after).

However when I look in the formula bar for that cell, the date shows as: 1/6/2016, not 6/01/2016.

So, first off, what is up with that? I have a column of these dates, how will they sort, by what I see in the cells, or what is referenced in the formula bar?


WorkSheet #2

In the other worksheet I have, I also have dates in the DMY format. These show as being in General format, so I know these are not dates, though they have date-like information in them.

I converted these General dates to DMY formatting using Text to Column feature. When done, some cells sorted to the left and some to the right.

The ones sorted to the right show a format of Custom (m/d/yyyy h:ss) - even though the original data came to me in DMY (in General format), not M/D/Y, and I used Text to Column convert it using D/M/Y.

To to be clear: it came to me in D/M/Y (General format, and D/M/Y is what it shows in the cell), and I converted it using DMY when I used Text to Column.

The cells that sorted to the left, show as still being in General format, even after being converted (via the same Text to Column operation).

And again, the cells sorted to the right are in the custom format referenced above.


Bottom line: What please do I need to do in order to get the cells of these individuals worksheets to be in D/M/Y (the way the were sent) to be actual dates, that will properly sort (based on D/M/Y date)?

I know the problems are (or may be) different for each sheet, but thought it best to lay out all the pieces here in the hopes that someone will have some ideas on how to work this out.


Thanks for reading - any and all thoughts are much appreciated!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
I'm using O365 in the US but have system time on my PC set for Australia (in case this matters…)
'
'
'
However when I look in the formula bar for that cell, the date shows as: 1/6/2016, not 6/01/2016.
This indicates that your Windows Region setting is U.S. regardless of your system time setting. Double check your Windows Regional Format setting and set it to Ausi-style.
 

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
89
This indicates that your Windows Region setting is U.S. regardless of your system time setting. Double check your Windows Regional Format setting and set it to Ausi-style.
well, what the heck?!

I can see immediately that this has made a difference. I have't yet had time to review the other issues enumerated above, but will guess that your hint will have fixed that all up.

Can't thank you enough AlphaFrog - I was going nuts... I really appreciate it, thx!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,476
Members
406,764
Latest member
ExcelMaker007

This Week's Hot Topics

Top