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