Date copy/paste problem

[libada]

I am using Excel 13 on Windows 7. Usually copying a worksheet is not a problem. However, on one worksheet I receive from a client (which contains dates, formatted with the custom format d,mmm), when I try to copy/paste it, all dates are changed on the new copy to one date earlier than they were on the original. None of the date cells are formulas. I have tried every way of "paste special" I can think of. Why is this happening? Is the worksheet corrupt?

All other sheets I copy which contain dates copy just fine.

Thank you.

 

[JeckeL]

The cell format is "d,mmm"? If you copy the excel cell and paste it into notepad what do you get?

 

[Guest]

little confused by what you mean by "all dates are changed on the new copy to one date earlier than they were on the original"

can you clarify?

also - excel 13? Excel 2013, yes?

 

[libada]

little confused by what you mean by "all dates are changed on the new copy to one date earlier than they were on the original"

can you clarify?

also - excel 13? Excel 2013, yes?

If the date on the original is November 6, 2014, appearing in the cell as "6-Nov" when I copy it to the new sheet, it changes to "5-Nov" for no apparent reason.

 

[libada]

little confused by what you mean by "all dates are changed on the new copy to one date earlier than they were on the original"

can you clarify?

also - excel 13? Excel 2013, yes?

Sorry - And yes, Excel 2013. My bad.

 

[libada]

The cell format is "d,mmm"? If you copy the excel cell and paste it into notepad what do you get?

The correct date, i.e. if the date in the cell on the original is November 6, 2014, appearing as "6-Nov" it appears the same way when pasted into notepad.

 

[JeckeL]

The cell format is "d,mmm"? If you copy the excel cell and paste it into notepad what do you get?

The correct date, i.e. if the date in the cell on the original is November 6, 2014, appearing as "6-Nov" it appears the same way when pasted into notepad.

What kind of format is the destination cell in excel 2013 (i.e. date, general, text, etc), and if you change the format type of the original cell (which is a custom '6-Nov' date) to short date do you get 11/6/2014? Is the time & date set correctly on your PC?

 

[USAFRet]

Is the client possibly in a different time zone?
5 Nov 2014 11 AM in New York is 6 Nov 2014 1:00 AM in Tokyo.

Excel doesn't store the 'date'. It stores the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day.
It then presents it to you in date format, relative to your time zone.

 

[libada]

Is the client possibly in a different time zone?
5 Nov 2014 11 AM in New York is 6 Nov 2014 1:00 AM in Tokyo.

Excel doesn't store the 'date'. It stores the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day.
It then presents it to you in date format, relative to your time zone.

Unfortunately, the client is in the same time zone as we are. Interesting information, though. Thank you.

 

[USAFRet]

Is he possibly on a Mac and you on Windows? Office for Mac date format starts at 1904 instead of 1900. Confusing and frustrating when you move files from one to the other.

 

[libada]

The cell format is "d,mmm"? If you copy the excel cell and paste it into notepad what do you get?

The correct date, i.e. if the date in the cell on the original is November 6, 2014, appearing as "6-Nov" it appears the same way when pasted into notepad.

What kind of format is the destination cell in excel 2013 (i.e. date, general, text, etc), and if you change the format type of the original cell (which is a custom '6-Nov' date) to short date do you get 11/6/2014? Is the time & date set correctly on your PC?

The destination cell is formatted by copying through "Paste, special, format and number values." If I change the format type of the original cell to show 11/6/2014, then try to copy/paste, the destination cell then shows 11/6/2010. Crazy. Yes, date, time and time zone are all set correctly on my PC.

 

[USAFRet]

The cell format is "d,mmm"? If you copy the excel cell and paste it into notepad what do you get?

The correct date, i.e. if the date in the cell on the original is November 6, 2014, appearing as "6-Nov" it appears the same way when pasted into notepad.

What kind of format is the destination cell in excel 2013 (i.e. date, general, text, etc), and if you change the format type of the original cell (which is a custom '6-Nov' date) to short date do you get 11/6/2014? Is the time & date set correctly on your PC?

The destination cell is formatted by copying through "Paste, special, format and number values." If I change the format type of the original cell to show 11/6/2014, then try to copy/paste, the destination cell then shows 11/6/2010. Crazy. Yes, date, time and time zone are all set correctly on my PC.

Mac vs PC. See above.

 

[libada]

Is he possibly on a Mac and you on Windows? Office for Mac date format starts at 1904 instead of 1900. Confusing and frustrating when you move files from one to the other.

Ah, another interesting piece of useful information. And yes, quite confusing and frustrating. What an odd place to start! Thank you for the info.