Date is being changed when workbook is being exported

Di27

New Member
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 PM24-06-2016 03:21 PM
24-06-2020 03:18 PM24-06-2016 03:18 PM
24-06-2020 03:18 PM24-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:
VBA Code:
    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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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 ?
 
Last edited:

Di27

New Member
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Di27,
Happy it helped and welcome to the board :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,490
Messages
5,548,356
Members
410,828
Latest member
A9Bosv3
Top