趣玩Excel之日历表，实现时间高效管理，你会吗？

文 / 雷哥

编辑 / 小鱼儿

大家好，想咨询下大家会使用Excel做日历表，以便统计出自己每天花费的时间吗？

雷哥给大家展示下下图的案例，输入起始时间，自动出现起始时间开始的7天日期，具体效果见下图所示。

实现以下特点

在右边的日历上可以显示出当月的日历

起始时间开始的一周内，颜色为深绿色

非本月的日期数据均使用灰色进行展示

输入本周当日每一事件花费事件，可以实现数据的自动统计

大家看完后，是不是感觉特别神奇好玩，且非常实用呢？

下面雷哥跟大家分享下如何做出这份高大上Excel日历表。

一、做出基础的时间管理表和日历表

根据表格样式，首先做出如下图所示的表格，相信这一步大家都没有什么问题。

二、设置时间管理表设置

step1：设置日期的显示格式。选中单元格A12:A18，右击设置单元格格式——自定义——设置为 “ddd m/d”。设置完成后，显示的效果如下图所示。

解释：ddd表示日期数据显示的为星期， m为月， d为日。因此ddd m/d 显示为星期+月+日。

step2：通过公式完善A列日期数据。在A13单元格中输入数据= A12+1，拖拽完成填充。具体效果见下图所示。

解释：日期的本质就是数值，加1，表示推后一天；减1，表示提前一天。

step3：对时间进行求和。

为了使得求和后的数据显示为常见的时间 00:00格式，首先对数据格式进行设置。具体操作如下：选中需要设置的单元格，右击——自定义——设置为 “[h]:00”即可。

下面开始对时间进行求和。在Total的行和列单元格中，利用sum函数求和即可。具体操作见下图所示。

设置完成后，效果如下所示。单元格中输入时间，会自动进行求和。

三、设置日历

step1：设置日历年份数据和月份数据。

思路：如果输入计划开始的时间，提取年份即可。但是如果没有输入时间，就显示空白是不是不太好看呀。雷哥改为不输入时间时，显示当前的年份。

使用公式 =IF(ISBLANK(G8),YEAR(TODAY()),YEAR(G8)) 即可实现我们上面的要求。

解释：=isblank(G8)，如果G8为空，则返还True，否则返回False；Year函数是提取日期年份的函数。看到这里，大家对公式是不是有了更加深入的了解？

解释：如果日期数据为空，则显示当前的年份2019；如果输入日期数据，则提取日期，比如输入2018/1/1，则显示2018

同理，设置日历的月份数据，使用的公式为=IF(ISBLANK(G8),MONTH(TODAY()),MONTH(G8))。具体效果见下图所示。

step2：设置年月。如图所示，单元格K3的格式设置为mmmm yyyy，单元格k3中输入公式=DATE(K2,P2,1)

k3单元格中输入=DATE(K2,P2,1), 其实使用数字组成了一个日期数据，显示的是某月的第一天。

step3：确定日历表中起始数字。

首先，按照习惯，第一列是星期日，依次是星期一到星期六。具体操作如下图所示。

下面我们来确定日历表中的起始时间。

思考：假如月初时间为2019年8月1日，星期四，起始时间为 =月初时间-4，即2019年8月1日（星期四）日历初始时间为2019年7月28日。

对上述公式理解后，初始时间公式为 =K3-WEEKDAY(K3,2)

step4：完善后续的日期数据。后一天的数据就是前一天加1即可。

step5：非本月的时间使用灰色标记。要想实现这种效果，需要使用条件格式，思路是日期数据非本月，则显示为灰色数据。具体方法如下

选中数据区域，单击开始——条件格式——管理规则——新建规则——使用公式确定要设置的单元格，输入公式=MONTH(K5)MONTH($K$3) 即可。单击确定后，可以发现，非本月的数据都已经显示为了灰色。

step6：标记本周的7天日期数据，加粗+单元格填充为绿色。

思路：只要日历中的数据跟A列的数据有完全吻合的，直接加粗和单元格填充即可。因此，还是选择条件格式进行填充。

选中数据，单击开始——条件格式——新建规则——使用公式确定要设置的单元格，输入公式=MATCH(K5,$A$12:$A$18,0)，分别设置字体加粗和单元格填充。

具体操作见下图所示。

四、效果展示

1. 输入起始时间2019年10月1日，可以发现，日历中10.1-10.7字体都加粗显示了，且10.1出现在星期二的位置上。

2.时间实现自动求和。如图所示，输入时间，可以统计出每天或者每一项/周（比如读书等）所花费的时间。

五、总结

今天雷哥跟大家分享了Excel时间管理表格制作方法，里面涉及的知识点有函数，条件格式等，希望大家可以掌握。有任何问题，随时可以留言沟通~

Copyright 2019 雷哥 @雷哥Office

All Rights Reserved.

欢迎大家关注

《竞争力：玩转职场Excel，从此不加班》作者！