Date changes when copied and pasted on another excel document

[Ntsikzoh]

Good Day.

Please kindly help. i have a problem with copying and pasting excel spreadsheet data with date from  another excel document to another. The date changes to a future date. 

The original data is as follows: 

SIDVOKODVO

MPAKA

MLAWULA

AVG

DATE

TRAIN

ENGINES

TONNAGE

AXLES

TRUCKS

DEP(dd/mm/yy hh:min)

ARR(dd/mm/yy hh:min)

DEP(dd/mm/yy hh:min)

ARR(dd/mm/yy hh:min)

DEP(dd/mm/yy hh:min)

ARR(dd/mm/yy hh:min)

(hh:min)

01/06/2022

8801

34694/804

980

196

49

01/06/22 17:16

01/06/22 13:52

3:24

8800

34694/804

2215

160

40

01/06/22 21:35

02/06/22 2:45

5:10

02/06/2022

8800

95001/004

1875

116

29

02/06/22 4:32

02/06/22 2:23

21:51

8801

34694/804

720

160

40

02/06/22 10:37

02/06/22 4:45

5:52

03/06/2022

8800

34694/804

2170

160

40

03/06/22 4:59

03/06/22 9:35

4:36

8803

95001/004

800

160

40

03/06/22 10:40

03/06/22 7:00

3:40

8802

95001/004

2227

160

40

03/06/22 17:37

04/06/22 3:45

10:08

The copied date changes as follows:

01/06/2022 changes to 02/06/2026

02/06/2022 changes to 03/06/2026

03/06/2022 changes to 04/06/2026

04/06/2022 changes to 05/06/2026

Please help i have tried all the paste options but none is giving me the right thing.

[#2 · Accepted solution]

@Ntsikzoh 

One workbook uses the so-called 1904 date system, and the other doesn't. The difference between the two systems is 4 years and 1 day (1462 days).

To correct the dates:

Enter 1462 in a cell.

Copy the cell.

Select the future dates.

Right-click in the selection and select Paste Special... from the context menu.

Alternatively, click the lower half of the Paste button on the Home tab of the ribbon and select Paste Special...

Select Subtract.

Click OK.

Repeat as necessary for other ranges.

Clear the cell with 1462 when you're done.

Remark: the 1904 date system can be toggled on/off in File > Options > Advanced, under 'When calculating this workbook'. It is a per-workbook setting.

[#3]

Thank you, thank you so much Mr Hans, you don't know how much i appreciate you for this. the File > Options > Advanced is the one that helped me. i appreciate.

[#4]

Good answer, thanks!

[#5]

Thank you!!