手机

密码

安全问题

注册 忘记密码?

详情请戳:https://www.udongman.cn/index.php?m=product&c=csp ​

有效期:2022/03/03 - 2022/08/30
Excel做时间统筹(工期管理、绩效管理)
来源: | 作者:wUw | 发布时间: 193天前 | 158 次浏览 | 分享到:

现在有一个需求:进度管理。示例表如下:


1、需要完成的任务:

1.1)从下单日期到完成日期的总时间的体现;

1.2)与当前时间对比,剩余的时间;

1.3)一个可以动态增长的进度条;

1.4)一个输入实际完成日期的地方;

1.5)根据实际完成日期固定超期情况,要显示具体的天数和时间。

2、实现过程:

2.1)从下单日期到完成日期的总时间的体现:

首先可以看到,要求完成日期只包含日期但不包含时间,现在人为规定,每天下午四点五十九分五十九秒为截止时间,因此有Excel函数公式如下:

=(DATEVALUE(要求完成日期单元格)+TIMEVALUE("16:59:59"))-(DATEVALUE(下单日期单元格)+TIMEVALUE(下单日期单元格))

解释:

a)DATEVALUE将规范格式的日期(例如“年-月-日”或“年/月/日”)转换为Excel识别的日期;

b)TIMEVALUE将规范格式的时间转换为Excel识别的时间;

c)以上公式就可以识别为,要求完成日期及人为确定的16:59:59减去下单日期及时间,得到一个总共的时间,总时间这里使用了单元格格式:d hh:mm,保证得到的时间为“天 二位时:二位分”。

2.2)与当前时间对比,剩余的时间:

公式如下:

=IF(完成日期单元格="",DATEVALUE(要求完成日期单元格)+TIMEVALUE(" 16:59:59")-NOW(),DATEVALUE(要求完成日期单元格)+TIMEVALUE(" 16:59:59")-完成日期单元格)

在完成之前,需要一直记录超期情况,以便对整体进度有所了解,所以当完成日期单元格没有填写信息的时候,剩余时间会与当前电脑时间进行比对,超期则呈现为负值,负的具体值则代表了超期多久,并使用了条件格式:

来醒目的显示当前状态,当然也可以酌情使用百分比,对于百分比来说,当工期并不长的时候,清晰的数字会更准确的把握当前情况。如果要时间相减显示负值,则按照以下设置:


2.3)一个可以动态增长的进度条:

公式:=(总时间单元格-剩余时间(天)单元格)/总时间单元格

条件格式:


2.4))一个输入实际完成日期的地方、根据实际完成日期固定超期情况,要显示具体的天数和时间:

2.4.1)首先完成日期这里可以设置一个空单元格为红的单元格格式,已明确还未有录入完成日期的项目;

2.4.2)超期情况,即要显示超期了多久,并以完成日期为截点,方便进行绩效管理。

公式如下:

=IF(AND(ABS(剩余时间(天)单元格)>0,ABS(剩余时间(天)单元格)<1),TEXT(剩余时间(天)单元格,"h:mm"),TEXT(剩余时间(天)单元格,"d h:mm"))

为什么要采取联合if来判定是否处于0-1之见呢?因为日期相减的实际情况是1900-1-1,也就是说,天数最低是1,那么对于不足1天的情况,如果规范格式为d h:mm,则会出现错误的最少1天的情况,因此对此进行筛除,当然只用ABS((剩余时间(天)单元格)>1应该也是完全没有问题的。

条件格式:

3)展示未超期、截止未超期、截止超期的三种情况:

1)未超期:

2)截止未超期:

3)截止超期:


  • 标题
  • 内容
  • 验证码
提交
标题内容提交时间
没有记录!