excel中日期根据起止时间自动递归的函数

通常在工程施工或者类似一线工作中，需要记录包含日期、起始时间、终止时间的相关原始记录。在excel中将A\B\C三列分别定位为日期、起始时间、终止时间，为方便电子版编制，现将日期设置为根据起止时间自动递归的函数。

我们以下图为例：

日期	时间		共计（h）
	起	止
3月3日	17:36	18:06	0.5
3月4日	18:06	2:06	8.0

 

其中A3=3月3日,B3=17:36,C3=18:06，

通常为了实现日期自动递归，A4=A3+IF(C4<B4,1,0)，通过起止时间大小判断日期是否增加。

但是如果终止时间通过输入共计时间来控制，即B4=C3=18:06,C4=B4+D4/24=2:06，A4中的逻辑判断C2<B2永远都会为false，即出现日期不会增加的情况：

日期	时间		共计（h）
	起	止
3月3日	17:36	18:06	0.5
3月3日	18:06	2:06	8.0

通过将日期单元格格式修改为常规即可发现，在这种逻辑下停止时间永远会比起始时间大，即使是凌晨也是第二天的凌晨，而不会像时间显示格式那样判断为当日时间比较大小。

日期	时间		共计（h）
	起	止
3月3日	0.733333333	0.754166667	0.5
3月3日	0.754166667	1.0875	8.0

可以通过取余函数MOD来去掉时间的日期积累，即C4=MOD(B4+D4/24,1)=2:06

此时C4单元格中的2:06实际数值为0.0875，可以用于当日时间比较。

接下来进一步探讨行与行之间如何规避引用数据受新增删除行影响，A4=A3+IF(C4<B4,1,0)，公式中出现了对A3的引用，但是如果我在A3\A4之间增加行，原A4单元格将会成为A5=A3+IF(C4<B4,1,0)，需要手动调整为A5=A4+IF(C5<B5,1,0)，十分繁琐，如何使公式只出现本行的引用，将单独的行进行模块化，以便灵活调整。

可以通过偏移函数即OFFSET函数实现，即将A4=A3+IF(C4<B4,1,0)修改为A4=OFFSET(A4,-1,0)+IF(C4<B4,1,0)，A4偏移-1行，0列，即取本单元格的上一个单元格，同理修改其他需要引用其他行数据的单元格，如起始时间，这样将所有引用都聚集在本行，实现行的模块化，规避了新增删除行后的公式手动调整，增加了工作效率。