A client has sent to me an Excel workbook, with .xlsx extension. It contains columns of amounts and dates. If I highlight a typical date cell it displays 20/8/15 in the cell and 20/08/2015 in the formula bar. So far so good. If I reformat the cell as "General" number type, it displays numerical value 40774.
I move the entire worksheet into another workbook (also type .xlsx) and all of the dates change.
In the new workbook, the date per the above example displays as 19/8/11 in the cell and 19/08/2011 in the formula bar. If I reformat it as General, the numerical value remains 40774.
The date per original workbook supplied by client is correct. How do I force it to show the same date in the destination workbook, and what is causing this effect? Any ideas?
Thanks
With kind regards
Clint Westwood
Replies (11)
Please login or register to join the discussion.
Almost certainly the 1904 date system is being used in workbook with the later date. Options - Advanced - When calculating this workbook - untick "Use 1904 date system". This is for Excel 2016 but the other versions are much the same.
PLAN B
https://support.office.com/en-gb/article/Change-the-date-system-format-o...
Scroll down to "I'm having problems . . . "
Can't believe Excel are still using 1904 software.
I had no idea Excel software was being written in 1904. Bill Gates must be older than he looks!
RM
Hi,
Your dates are giving you problems because Excel for Windows and Excel for Mac use different earliest dates supported by them.
Excel for Windows uses January 1, 1900 as day 1, while Excel for Mac uses January 1, 1904 as day 1 for calculating dates.
Your client's workbook has been created using Excel for Windows while in all probability the other workbook has been created using Excel for Mac. That is why your dates are showing a difference of 4 years.
Rakesh Agrawal
You can force the display of date by formatting the column as date. Excel actually stores the data as 40774 for the date 19/08/2011.
If I enter 40774 into a cell and then format as date I get 19/08/2011.
If I look at the format of a cell I see that it is general. If I then enter 19/08/2011 and look at the format I see that it has changed from General to Date.
When you copy and paste do you paste as values?
Rich