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!
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!
