Forum Discussion
Solved
Date changes when copied and pasted on another excel document
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.
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.
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.
- Thank you!!
- Good answer, thanks!
- 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.
