Excel ActiveX组合框将所选日期显示为数字而不是日期

问题描述

我有一个ActiveX组合框.它的ListFillRange是另一个工作表上的范围.它找到日期，但是当我选择它们时，我得到了这个奇怪的值.有人知道最新情况以及如何解决吗?

I have an ActiveX combobox. Its ListFillRange is a range on another sheet. It finds the dates, but when I select them I get this weird value. Anybody know whats up and how to fix it?

我曾尝试通过VBA代码对其进行修复，但同样的问题，我将不胜感激.

I have tried fixing it through VBA code but its the same problem, I would really appreciate it.

示例代码:

Private Sub ProdDateCombobox_DropButtonClick()
ProdDateCombobox.ListFillRange = "ProductionList"
End Sub

ProductionList是具有日期的单元格D2; D100的范围

ProductionList being a range of cells D2;D100 with dates

推荐答案

您得到的数字是自1900年1月1日以来的天数-这是Excel存储日期的方式.

The number you're getting is the number of days since 1st January 1900 - this is how Excel stores dates.

该问题的一种解决方案是:

您将日期格式设置为日期，范围为 D2:D100 ，命名为 ProductionList .

You have your dates, formatted as dates, in the range D2:D100 named as ProductionList.

当您选择下拉菜单时，日期会正确显示在下拉菜单中，但所选日期将显示为数字-2018年7月4日，显示数字43285(自1900年1月1日以来的天数).

When you select the drop-down the dates appear correctly in the drop-down, but the selected date appears as a number - 4th July 2018 showing the number 43285 (days since January 1st 1900).

• 在空白区域中输入公式 = TEXT(D2，"dd/mm/yyyy")，其中 D2 是 ProductionList 命名范围.将公式向下拖动到日期的末尾.
• 仅在原始列表上复制此新范围的公式并将粘贴特殊作为值.

• In a blank range enter the formula =TEXT(D2,"dd/mm/yyyy") where D2 is the first date in your ProductionList named range. Drag the formula down to the end of the dates.
• Copy this new range of formula and paste special as values only over your original list.

您的原始列表现在应该可以在组合框中正确显示，但是在尝试将日期与链接的单元格进行比较时会遇到问题.

Your original list should now show correctly in the combo-box, but you'll have problems when trying to compare dates to your linked cell.

例如，如果链接的单元格是 A5 ，则公式 = MATCH(DATE(2018,3,1)，$ A $ 5,0)将返回#N/A .

For example, if your linked cell is A5 the formula =MATCH(DATE(2018,3,1),$A$5,0) will return #N/A.

要解决此问题，请在单元格 B5 中输入一个公式: = A5 + 0 并与此进行比较.在文本日期上加上0的行为将迫使其重新计算为真实日期.

To solve this enter a formula in cell B5: =A5+0 and compare against this. The act of adding 0 to your text date will force it to recalculate as a real date.

这篇关于Excel ActiveX组合框将所选日期显示为数字而不是日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！