Sorting Problem

[lharesign]

Hi all,

I have a very strange problem with excel that I was hoping someone would be able to help with. I use a spreadsheet in work and sort it on the "off press date" to set them into date order. The cells in this column are pulling data from various points of the spreadsheet using a formula that was put in place by my predecessor and in all honesty goes a little over my head. It is: =IF(AY8=0,IF(IF(ISERROR(VLOOKUP(X8,KBApressdate!A$2:B$4979,2,FALSE)),"",VLOOKUP(X8,KBApressdate!A$2:B$4979,2,FALSE))="",H8+AX8,IF(ISERROR(VLOOKUP(X8,KBApressdate!A$2:B$4979,2,FALSE)),"",VLOOKUP(X8,KBApressdate!A$2:B$4979,2,FALSE))),AY8)

The cells are formatted as "Custom" and sort correctly on my work PC, taking the answer to these formulae as dates (they currently appear as 17/04/18 for example). When attempting to access this remotely using an RDS and Excel 2013, although the formula, formatting of the cell, etc are all exactly the same, it sorts the dates as numbers, i.e. 01/01/2018, 01/02/2018, 02/01/2018, etc.

This problem seems intermittent and not purely down to the different versions of excel. If logged into the remote desktop as the administrator it will sort correctly; if logged in on my personal account on the remote desktop it sorts it as numbers rather than dates.

Does anyone have any suggestions please? I have spent all morning trying to figure it out! I have already attempted to set the format the cells as "date" and that hasn't worked. To me it seems as though maybe there's a setting that says "Sort anything that looks like dates as dates" that hasn't been selected, but I can't seem to find anything like that.

Thanks!

 

[mole999]

when you say numbers do you mean they look like numbers ?

 

[lharesign]

Hi Mole,

Thanks for the reply. No they are not appearing as numbers, they still appear as dates, they simply sort in a seemingly numerical order (except the first which appears to be an anomaly).

Here is a screenshot of it sorting correctly: https://drive.google.com/file/d/1hlvoJBHs2QNUU_NWc6HFXQ1yt8hjiysq/view?usp=sharing

Here is a screenshot of it sorting incorrectly: https://drive.google.com/file/d/1rv4oKmuDliNM7b8nnINJESbTXevs_chi/view?usp=sharing

The column in question is column 5 "Off Press Date", (in the spreadsheet itself it is column S for formula purposes).

 

[lharesign]

Sorry, I forgot to mention that in the screenshot of it sorting incorrectly it is only near the bottom that you can tell that it is sorting incorrectly - after the 12th.

 

[mole999]

I think you might have the European vs USA issues 13/12/00 12/13/00 are both valid but anything up to 12 might being seen as month/day or day/month. So the first place to look is in the control panel and see if both systems are using the same date format, then check what excel expects

 

[lharesign]

Thanks for the suggestion. That doesn't seem to be the case as the RDS admin and individual users (all based on one server) were providing different sorts (and they were set up with the same DDMMYY set up). Regardless, I seem to have accidentally solved my problem! I was fiddling around and it came up with a pop up asking if I wished to sort anything that looked like numbers as numbers, I hit yes, and it has worked correctly ever since.

Not sure how I managed to get that pop up to happen though.

Thanks for the help though!