工作中会经常收到同事发来的包含不规范日期值的报表,从而导致后期运算出错。例如“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等格式都属于不规范的日期值,无法使用日期函数对它们执行运算。是否可以在前期设计表格模板时禁止用户输入不规范的日期?

解题步骤

假设有图3.6所示的销量报表模板,要求对A2:A9区域加以限制,从而提升日期值的准确性,操作步骤如下。

Excel 如何禁止输入不规范的日期-Excel22

图3.6 销量报表模板

1.选择A2:A9区域。

2.单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“日期”,表示只允许在 A2:A9 区域输入日期。

4.根据实际工作需求对“开始日期”和“结束日期”指定范围。例如,当前工作表只允许输入2015年和2016年的日期,那么就分别输入“2015-1-1”和“2016-12-31”。设置结果如图3.7所示。

5.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“请按标准的日期格式输入,有效范围在2015-1-1到2016-12-31之间”,设置结果如图3.8所示。

Excel 如何禁止输入不规范的日期-Excel22

图3.7 指定日期范围

Excel 如何禁止输入不规范的日期-Excel22

图3.8 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面,在 A2 单元格输入不规范的日期“2015.2.28”,当单击Enter键后将弹出图3.9所示的错误提示信息。

Excel 如何禁止输入不规范的日期-Excel22

图3.9 输入格式不规范的日期时产生错误提示

7.单击“重试”按钮,继续输入日期“2014-5-7”,Excel会产生相同的提示信息,表示格式有误、范围有误时都禁止操作。

知识扩展

1.“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等都不是规范的日期,所有日期函数都无法对它们执行日期运算。

例如,Month函数可以计算一个日期属于几月份,图3.10和图3.11两个图片分别展示了错误格式的日期和正确格式的日期的运算结果。

Excel 如何禁止输入不规范的日期-Excel22

图3.10 错误的日期执行日期运算时出错

Excel 如何禁止输入不规范的日期-Excel22

图3.11 正常计算出月份

2.正确的长日期有三种格式(排除英文格式),包含“2015-7-15”、“2015/7/15”和“2015年7月15日”。当在单元格中输入最后一种格式的日期时,Excel会自动将它转换成“2015/7/15”格式的日期。

3.日期其实就是数值,只是它呈现出的形态与数值不同而已,本质上是一样的。例如2015年8月8日等同于数值42224,只是显示为日期样式而已。因此日期必须是数值,不管其外观如何变化。假设按文本形式输入日期则无法参与数值运算,对工作带来负面影响。“2015.8.8”就是文本形式,所有日期函数都无法对该值执行日期运算。

4.为什么42224代表2015年8月8日呢?Excel以1900年1月1日作为初始日期,日期1900年1月1日被当作第一天,那么相对于1900年1月0日之后的第42224天则刚好是2015年8月8日。可以如此验证:在A1单元格录入数值42224,然后依次单击功能区的“开始”→“数字格式”→“长日期”,单元格中的42224马上会显示为2015年8月8日,效果如图3.12所示。

Excel 如何禁止输入不规范的日期-Excel22

图3.12 通过设置格式改变数值的显示形态