excelexcel-formulaexcel-2016

Excel calculating different dates using same formula, between server and local installation

I work with Excel files which I prepare on a server and then copy locally so that they can be emailed out. I've found that after I copy the finished files to my local machine, the installation of Excel on my local machine must interpret dates differently from the installation on the server.

Server version

Microsoft® Excel® 2016 (16.0.5443.1000) MSO (Version 2402 Build 16.0.5443.1001) 32-bit

Local version

Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20282) 64-bit

In both files the referenced J4 cell contains the date 05/01/2024. The server appears to be American and thinks that this is the 1st of May, while my machine must be British and sees the 5th of January. I'm based in the UK but working with American software so the cell should indeed be interpreted as the 1st of May. In any case I would just like it to be consistent so that I can prepare my files and email them out.

Solution

  • Since the value of J4 is a string value, Excel is implicitly converting the value to a date in the EOMONTH function. The implicit conversion on the server is using the American regional settings. The implicit conversion on the local machine is using the British regional settings.

    One possible solution is to use string manipulation and assume the source will always be in an American date format.

    Try this:

    = EOMONTH( DATE( VALUE( RIGHT( J4, 4 ) )
               , VALUE( LEFT( J4, 2 ) )
               , VALUE( MID( J4, 4, 2 ) ) ), 0 )