Copy & Paste changes date format

Question

I am trying to either link to data in another workbook or even copy it but when I either link to it or I copy & paste it (yes I've tried the paste special option to Keep source formatting) the date format changes.

Source Data cell number format: Date 14/3/01 - Sample 14/9/13

Destination cell number format: Date 15/3/05 - Sample 15/9/17

As you can see from the Sample in the Format Cells window when I paste the date in or pull it in with a formula it appears as 15/9/17 instead of the original 14/9/13.

Stumped absolutley stumped.

Answer 1

Spot on thank you.

Now I have to work out what to do to convert it back to standard date format. It's a large file full of dates as it's my business accounts workbook.

Type 1462 in any unused cell,

Copy that cell then select all cells with dates then paste special>subtract>ok>esc

With date cells still selected, re-format to date.  Delete the 1462

Go into options and uncheck the 1904 date system.

Gord

Answer 2

I am trying to either link to data in another workbook or even copy it but when I either link to it or I copy & paste it (yes I've tried the paste special option to Keep source formatting) the date format changes.

 

Source Data cell number format: Date 14/3/01 - Sample 14/9/13

Destination cell number format: Date 15/3/05 - Sample 15/9/17

 

As you can see from the Sample in the Format Cells window when I paste the date in or pull it in with a formula it appears as 15/9/17 instead of the original 14/9/13.

 

Stumped absolutley stumped.

One of the workbooks was saved using 1904 date system which is a setting in options.

Subtract 1462 day from 15/9/17

Gord

Answer 3

Spot on thank you.

Now I have to work out what to do to convert it back to standard date format. It's a large file full of dates as it's my business accounts workbook.