vba 为什么我的 Excel 电子表格将我的数字格式化为日期？

Why do my Excel spreadsheets format my numbers as dates?

提问by Robbie Rosati

We have a lot of automated spreadsheets that extract data, calculate forecasts, publish web pages, etc. It's a really messy system, and I'd love to redo it (with, say, a real web service), but we don't have permission to do that.

我们有很多自动电子表格，可以提取数据、计算预测、发布网页等。这是一个非常混乱的系统，我很想重做（例如，使用真正的 Web 服务），但我们不这样做有权限这样做。

We run Excel 2007, with most of the spreadsheets converted to .xlsms.

我们运行 Excel 2007，将大部分电子表格转换为.xlsms。

Anyway, for some reason unknown to me, certain spreadsheets will format certain numeric cells as dates at seemingly random times. As you might imagine, the problem is difficult to track down, and usually I only find out when someone has written a nasty email about our data reading 3-Feb-1901when it should read 400(apparently Excel thinks there was a leap year in 1900).

无论如何，出于某种我不知道的原因，某些电子表格会将某些数字单元格格式化为看似随机时间的日期。正如您可能想象的那样，这个问题很难追踪，通常我只会在有人写了一封关于我们3-Feb-1901应该阅读的数据读取的令人讨厌的电子邮件时才发现400（显然 Excel 认为 1900 年是闰年）。

I've explicitly set the cells in question to numeric format several times, only to find them filled with dates a week later.

我已经多次明确地将有问题的单元格设置为数字格式，但一周后才发现它们填充了日期。

I've scanned the macros (many are in the ancient Excel 4.0 macro language), they appear to be clean of any formatting changes. All copy/pastes are done as values and do not preserve source cell formatting.

我已经扫描了宏（许多是古老的Excel 4.0 宏语言），它们似乎没有任何格式更改。所有复制/粘贴都作为值完成，不保留源单元格格式。

There are no conditional formatting rules in the sheets in question.

有问题的工作表中没有条件格式规则。

Adding a little VBA to format the cells as desired in the Auto_Opensubroutine appears to work around the issue.

添加一点 VBA 以根据Auto_Open子例程中的需要格式化单元格似乎可以解决此问题。

Is this fix good enough, or in a week will I find the date format is appearing mid-calculation? If not, what is the root cause and how I can fix it?

这个修复是否足够好，或者一周后我会发现日期格式出现在计算中间？如果不是，根本原因是什么，我该如何解决？

One thing: I just read from this sourcethat Excel '07 may spontaneously change Normal-styled cells to the format [$-409]m/d/yy h:mm AM/PM;@. Some of my cells that have changed were definitely styled Normal, but evidently not all.

一件事：我刚刚从这个来源读到Excel '07 可能会自发地将Normal样式单元格更改为格式[$-409]m/d/yy h:mm AM/PM;@. 我的一些已更改的单元格确实具有样式Normal，但显然不是全部。

采纳答案by Brian Vanderbusch

I'm assuming the cells in question are the receipients of copied/pasted data, or external/queried data?

我假设有问题的单元格是复制/粘贴数据或外部/查询数据的接收者？

Whenever you add pasted data to a cell in excel, it runs througha routine to determine the best format for anything that's listed as "general" format, even if the source is "general" or "number". Sometimes even setting the destination cells to "number" format does not clear up this issue. But I've found the only way to to get this to work the way you need it to is to explicitly declare the format (in the macro) to the destination range in question before the paste/update/refresh operation.

每当您将粘贴的数据添加到 Excel 中的单元格时，它都会运行一个例程，以确定列为“常规”格式的任何内容的最佳格式，即使源是“常规”或“数字”。有时甚至将目标单元格设置为“数字”格式也不能解决这个问题。但是我发现让它按照您需要的方式工作的唯一方法是在粘贴/更新/刷新操作之前将格式（在宏中）显式声明为相关目标范围。

I also think there's a way to disable date recognition within your VBA, but I can't remember the exact code. Try recording a macro while doing manually importing or performing your update and you should be able to use what comes up.

我也认为有一种方法可以在 VBA 中禁用日期识别，但我不记得确切的代码。尝试在手动导入或执行更新时录制宏，您应该能够使用出现的内容。

Edit: just did a test run, and after your connection string and destination add:

编辑：刚刚进行了测试运行，并在您的连接字符串和目的地之后添加：

.WebDisableDateRecognition = True

That should definitely do the trick if you're pulling data in from an external source and not copy/pasting.

如果您从外部来源提取数据而不是复制/粘贴，那绝对可以解决问题。

回答by jonathan

Just in case this helps, i had an issue where excel was auto formating my info pulled from a webpage into DATE format, to fix, in the import window, click options and UNCHECK the auto date formating option, after that, my data imported perfect!

以防万一这有帮助，我遇到了一个问题，即 excel 将我从网页中提取的信息自动格式化为 DATE 格式，以修复，在导入窗口中，单击选项并取消选中自动日期格式选项，之后，我的数据导入完美！