获取两个日期时间单元格之间的差异 [英] Get the Difference between two date-time cells

问题描述

我需要考虑仅工作日的小时数，以获取两个日期时间单元格之间的差异.

I need to get the difference between two date-time cells in hours considering only weekdays.

示例:

25/02/2018 16:00:00 | 26/04/2018 09:00:00 | 19:00:00

推荐答案

使用网络天数:

=(NETWORKDAYS(A1,B1)-IF(WEEKDAY(A1,2)<6,MOD(A1,1))-IF(WEEKDAY(B1,2)<6,1-MOD(B1,1)))*24

这是指从第一天到第二天的工作天数.

This says count working days from first date to second date inclusive.

如果第一个日期是工作日，请减去开始时间之前未工作的小时数.

If first date is a working day, subtract hours not worked before start time.

如果第二个工作日为工作日，请减去完成时间后未工作的小时数.

If second date is a working day, subtract hours not worked after finish time.

严格来说，您还应该检查开始日期时间是否早于结束日期时间.

Strictly speaking, you should also check that start datetime is before finish datetime.

这篇关于获取两个日期时间单元格之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！