Date is being changed when workbook is being exported

[Di27]

Hi,

I am currently working on a VBA code that has a specific button to export a copy of the worksheet in question.

The table has a column for date and time together in this format: dd-mm-yyyy hh:mm AM/PM;@
The import of the data into this worksheet works fine, however when exporting the worksheet via VBA code for some reason it keeps changing the year on the column.

for example it starts out correctly (left column and becomes the right column after export)

24-06-2020 03:21 PM	24-06-2016 03:21 PM
24-06-2020 03:18 PM	24-06-2016 03:18 PM
24-06-2020 03:18 PM	24-06-2016 03:18 PM

However the original file still has the correct data and only the newly generated workbook is showing the incorrect information.

The code I use to export is:

    Application.ScreenUpdating = False

        ThisWorkbook.Sheets("Account Summary").Copy Before:=wb.Sheets(1)
        ActiveWorkbook.SaveCopyAs (filePath & FileNames & " - System Summary.xls")
        ActiveWorkbook.Close 0
    
    Application.ScreenUpdating = True

While trying to debug the issue, I found that when I manually go to save the file instead of using the above code it works perfectly fine so I am really confused what could be going wrong.

 

[Yongle]

Welcome to MrExcel

Strange behaviour indeed ?

Are those Date&Time cells values or formulas ?
Why is workbook saved with file extension xls ?
ThisWorkbook and ActiveWorkbook - are they the same workbook ?
If not, which workbook is wb - ThisWorkbook or ActiveWorkbook or another workbook ?

 

[Di27]

Welcome to MrExcel

Strange behaviour indeed ?

Are those Date&Time cells values or formulas ?
Why is workbook saved with file extension xls ?
ThisWorkbook and ActiveWorkbook - are they the same workbook ?
If not, which workbook is wb - ThisWorkbook or ActiveWorkbook or another workbook ?

Thank you for your response.

The date&time cells are values that were imported from a .csv file. I've tested the cells and it is converting correctly as date & time. For some reason it just keeps changing the date and I am not sure what could possibly be causing this.
The workbook is being used to import data from multiple .csv files then is compiled and is exported as xls as it is used for backup purposes only and works with the current system best. I tried saving it as .xlsm or .xlsx however it makes no difference the year will always change for whatever reason.
Essentially ThisWorkbook, ActiveWorkbook and wb are all the same. I originally used just wb but due to ongoing errors with the dates I thought I'd try using ThisWorkbook or ActiveWorkbook instead to see if it may fix the issue one way or another. As I expected it didnt.

 

[MARK858]

Under File - Options - Advanced - Under when calculating this workbook is "use 1904 date setting" checked? Just trying to think why the difference is 4 years.

 

[Di27]

Under File - Options - Advanced - Under when calculating this workbook is "use 1904 date setting" checked? Just trying to think why the difference is 4 years.

omg that was it.

I've spent hours trying to figure out what was going on.. thank you so much you have saved me from almost going bald from pulling all my hair out

 

[Yongle]

@MARK858 - superb lateral thinking!

 

[MARK858]

@Di27,
Happy it helped and welcome to the board