日期的本质是数字

如图所示,当你把单元格从日期格式改为常规,就会发现,日期的本质是数字。1900年1月1日是数字1,按照天数递增,所以1900年1月31日就是31,2022年4月21日就是44672。

知道这一特性,我们就可以将数字按1递增,这样日期就自动变化了。

DATE函数

格式:DATE(年,月,日)

如上图所示,在D2单元格使用DATE函数引用了A1、A2、A3单元格,得出了2022年4月21日的日期。根据日期本质是数字的特性,在D2单元格上加1,即可得出4月22日这个日期。依次类推,从E2单元格开始,只需向右填充单元格,之后的日期也就自动生成了。如下图:

日期显示为星期

首先我们将从D2到G2的数据复制一遍,如图:

之后我们将第3行,从D3到G3的数据换一种单元格格式,自定义为 aaa,就可显示出星期了。

如果你想以星期一这样的形式显示,把 aaa 改成 aaaa 就行了。如果你想显示成周一这种形式,把 aaa 改成 "周"aaa就可以了。由于方法都大同小异,这里就不上图了。

使用数值调节钮显示日期

效果如下图所示:

若想实现上述功能,我们得首先启用开发工具。步骤是 文件→选项→自定义功能区,勾选开发工具,最后点击确定,如下图:

插入数值调节按钮,步骤是 开发工具→插入→表单控件→数值调节钮,如下图所示:

按钮的大小,大家按照个人喜好调节大小即可。接下来我们要设置控件格式,具体步骤是右键按钮,点击设置控件格式就行了。

第一个按钮的设置如下:

剩下的两个依次类推,月份的范围是1-12,以A2单元格显示。天数范围是1-31,以A3单元格显示。这样我们就可以轻松实现示例图中的效果哦。

限定日期显示

由于一个月最多31天,因此我们需要准备31个单元格。但有的月只有30天,特别是平年的2月只有28天。这样就会导致我们不想要的日期也会被显示出来。如下图:

因此有必要加上条件来限制日期的显示:当实际月份大于DATA函数中取值的月份时,月份不显示。

使用 IF 和 MONTH 函数即可解决。

IF函数格式=IF(条件,符合条件的结果,不符条件的结果)

MONTH函数=MONTH(日期),返回值为代表月份的数字,如4月30返回4,5月1返回5。

那么结合以下的图片:

如果E2单元格的日期月份大于A2单元格上的月份,那么就显示为空值(用""显示),否则显示为D2+1。所以具体的函数为:

=IF(MONTH(E2)>A2,"",D2+1)

但是我们发现在EXCEL中无法自己引用自己,所以要将公式中的E2改为D2+1。在向右填充的过程中,A2会变成B2、C2......,因此我们要将A2进行绝对引用(快捷键为F4或Fn+F4),这样A2的值就不会变了。

因此修改后的公式为:

=IF(MONTH(D2+1)>$A$2,"",D2+1)

但是公式还是有点缺陷的,比如2月:

由于2月只有28天,所以AF2显示为了空值。这就导致后面的AG和AH直接报错了。这时IFERROR函数就该登场了。

IFERROR函数:如果公式报错,显示为特定值。

格式=IFERROR(公式,若报错显示的特定值)

如果公式报错,显示为空值就好了,所以最终的公式为:

=IFERROR(IF(MONTH(D2+1)>$A$2,"",D2+1),"")

变换颜色

上面几步已经将动态日期全部完成了,如果我们想将周六周日自动加颜色,如下图所示:

这又该如何完成呢?

首先我们选中D2到AH2这个范围,然后在 开始 选项卡下,点击 条件格式新建规则,如图:

然后选择使用公式确定要设置格式的单元格,在公式框中输入公式,最后点击格式,选择喜欢的颜色填充就完成了。如图:

由于我们的需求是周六周日变颜色,即 星期>5,因此我们可以使用 WEEKDAY 函数完成需求。

WEEKDAY函数:返回星期的具体数值,周日返回1,周六返回7(西式);周一返回1,周日返回7(中式)

WEEKDAY函数格式:

​ 西式:=WEEKDAY(日期,1)

​ 中式: =WEEKDAY(日期,2)

根据我们的习惯,选择第二种方式,因此公式为:

注意:图中区域的公式默认为锁行锁列的绝对引用,若希望公式只在行生效,需要锁行不锁列,删掉列前面的$符号,反之亦然。

公式设置好后,点击格式→填充,设置一个自己喜欢的颜色。日期设置好后。重复以上步骤,将公式中的起始单元格设为D3,第二行的星期也就OK了。

最后修改:2022 年 04 月 27 日 02 : 31 PM
如果觉得我的文章对你有用,请随意赞赏
END

    本文作者:
    文章标题:Excel中动态日期的制作
    本文地址:https://cry33.com/archives/983.html
    除非另有说明,本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议
    版权说明:若无注明,本文皆逍遥隐士原创,转载请保留文章出处。