Automatically Formatting to Date upon Closure

Tidr0001

New Member
Joined
Sep 25, 2013
Messages
12
Simple but frustrating.

I have found that periodically Excel is automatically reformatting everything in large workbooks to Date Format. Initially I save these workbooks with General, Number, Currency, Text, and Date formatting within the cells. Upon opening at a later time, everything is converted to Date Format.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
I can think a few possible causes for this:
1. The file is not being saved to an Excel format, but something else, like a CSV or another kind of text or database file.
2. There is some VBA code (macros) which run automatically upon opening the files.
3. The workbook contains linked data (so it is pulling data from somewhere outside the workbook).

Do you have any of these situations?

If not, please provide more details, maybe exactly the steps that you are doing, what version of Excel you are using, and how you are saving the file.
 

Tidr0001

New Member
Joined
Sep 25, 2013
Messages
12
I can think a few possible causes for this:
1. The file is not being saved to an Excel format, but something else, like a CSV or another kind of text or database file.
2. There is some VBA code (macros) which run automatically upon opening the files.
3. The workbook contains linked data (so it is pulling data from somewhere outside the workbook).

Do you have any of these situations?

If not, please provide more details, maybe exactly the steps that you are doing, what version of Excel you are using, and how you are saving the file.

1. The file is not being saved to an Excel format, but something else, like a CSV or another kind of text or database file.
Majority of my workbooks are xlsx.

2. There is some VBA code (macros) which run automatically upon opening the files.
For security, Macros automatically get block until I enable. These workbooks do not have any VBA in them.

3. The workbook contains linked data (so it is pulling data from somewhere outside the workbook).
No linked data. I created the file. And again, Macros and links are automatically block until I enable.

I am using 2007. Typically saving via the menu and then closing. I am concerned the size of the files or the amount of cumputation is causing the issue.

Files are around 85 mb with around 7.5 million calculations, index / match, and if statements.
Already slimmed aout the file as much as possible.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
The file is not being saved to an Excel format, but something else, like a CSV or another kind of text or database file.
There's your problem right there! Text files (like CSV) do NOT save any formatting, just strictly text.
So every time you re-open a CSV file in Excel, no formatting is brought in so Excel goes back to its default, based on what it thinks the type of data it is.
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top