Dates changing in Excel

Dates changing in Excel - version issue??

Didn't find your answer?

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.

Replying to runningmate:
avatar
By nogammonsinanundoubledgame
29th Jul 2016 16:56

This looks just the ticket. Only problem is, despite my being logged in under user name, when I click on the link it opens up a web page with the first few paragraphs displayed, and requires me to log in again in order to view it. only trouble with that is that it rejects my ID/password if I try (despite that it works to get to the page that links to it).

Thanks (0)
Replying to nogammonsinanundoubledgame:
avatar
By paulwakefield1
29th Jul 2016 17:06

That's because it is the US site not the UK site I suspect.

Thanks (1)
Replying to nogammonsinanundoubledgame:
Brunel
By Brunel
30th Jul 2016 10:11
Thanks (0)
avatar
By paulwakefield1
29th Jul 2016 15:55

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.

Thanks (1)
Out of my mind
By runningmate
30th Jul 2016 11:59

PLAN B
https://support.office.com/en-gb/article/Change-the-date-system-format-o...

Scroll down to "I'm having problems . . . "

Thanks (0)
RLI
By lionofludesch
30th Jul 2016 14:41

Can't believe Excel are still using 1904 software.

Thanks (0)
Replying to lionofludesch:
Out of my mind
By runningmate
30th Jul 2016 16:42

lionofludesch wrote:

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
Thanks (1)
avatar
By rakesh1201
13th Aug 2016 06:24

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

Thanks (0)
avatar
By richwhight
19th Aug 2016 13:02

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

Thanks (0)
Replying to richwhight:
avatar
By richwhight
19th Aug 2016 13:05

And just realised it doesn't answer the question :-O

Thanks (0)