跳到主要内容

如何在Excel中计算两个日期之间(周末或节假日除外)的净工作时间?

作者:孙 最后修改时间:2020-05-07

在许多公司中,员工是按工作时间支付工资的。 计算一天的净工作时间很容易,但是如何计算日期范围内的净工作时间呢? 为此,本文介绍了在Excel中计算两个日期(周末和节假日除外)之间的净工作时间的公式。

计算工作日(不包括周末)

计算工作时间,不包括周末/节假日


箭头蓝色右气泡 计算工作日(不包括周末)

在这一部分中,我将介绍用于计算两个日期时间(不包括周末)之间的工作日的公式。

1.选择两个单元格,分别输入开始日期时间和结束日期时间,然后右键单击以选择 单元格格式 形成上下文菜单。 看截图:
doc网络工作时间1

2.在 单元格格式 对话框中,单击 联系电话 选项卡,然后选择 定制 形成 分类 列表,然后输入 m / d / yyyy h:mm Type 右侧的文本框。 看截图:
doc网络工作时间2

3。 点击 OK。 然后分别在两个单元格中输入开始日期时间和结束日期时间。 看截图:
doc网络工作时间3

4.在这两个单元格旁边的单元格中,例如C13,输入此公式 =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1),然后按 输入 键,您将获得具有自定义格式的结果,选择结果单元格,然后单击 主页 标签,然后转到 数字格式 列表以选择常规以将其格式化为正确的格式。 看截图:
doc网络工作时间4


箭头蓝色右气泡 计算工作时间,不包括周末/节假日

如果要计算不包括周末或节假日的净工作时间,可以按以下方法进行:

计算不包括周末的净工作时间

1.选择两个单元格并将其格式化为自定义格式m / d / yyyy h:mm,然后输入开始日期时间和结束日期时间。 看截图:
doc网络工作时间5

doc网络工作时间6

2.在单元格(例如C2)旁边,输入此公式,
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
express 输入 键,然后您将得到一个数字字符串。 看截图:
doc网络工作时间7

3.右键单击数字字符串,然后单击 单元格格式 从上下文菜单中 单元格格式 对话框中选择 定制 申请 分类 在下面列出 Number标签,然后输入 [h]:毫米 进入类型文本框。 看截图:
doc网络工作时间8

4。 点击 OK。 现在将计算两个日期之间(周末除外)的净工作时间。
doc网络工作时间9

Tips::在公式中,A2是开始日期时间,B2是结束日期时间,每天的8:30和17:30是一般的开始时间和结束时间,您可以根据需要进行更改。

计算净工作时间,不包括周末和节假日

1.与上述相同,选择两个单元格并将其格式化为自定义格式 m / d / yyyy h:mm,然后输入开始日期时间和结束日期时间。
doc网络工作时间10

2.选择一个空白单元格,然后在其中输入假日日期,这里我有3个假日,并分别在H1:H3中键入。 看截图:
doc网络工作时间11

3.选择一个空白单元格,它将放置计数结果,例如C2,
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
并按下 输入 键,您将获得一个数字字符串,并将其格式化为自定义格式 [h]:毫米。 看截图:
doc网络工作时间12

Tips::在公式中,A2是开始日期时间,B2是结束日期时间,每天的8:30和17:30是一般的开始时间和结束时间,H1:H3是假日单元格,您可以更改它们根据需要。

在Excel中轻松地将天/年/月/小时/分钟/秒添加到日期时间

假设您在一个单元格中有一个日期时间格式数据,现在您需要在该日期中添加若干天,年,月,小时,分钟或秒。 通常,使用公式是所有Excel用户的第一种方法,但是很难记住所有公式。 用 Kutools for Excel日期和时间助手 实用程序,您可以轻松地将天,年,月,小时,分钟或秒添加到日期时间,此外,您可以计算出日期差或基于给定生日的年龄,而无需记住任何公式。 点击即可获得 30 天的全功能免费试用!
doc第二小时添加小时
 
Kutools for Excel:拥有300多个便捷的Excel加载项,可以在30天内免费试用。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (68)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi , thank you for the formula, it really helped me in working out hours but my team works 10 hours a day only weekdays from 8am - 6pm , but i have a question , if i work 10 hours a day ,it means i am working 5 days (50 hours) . Pleae find the sample data below.

TASK-1 01/14/2022 19:18:25 01/14/2022 19:18:25 Days:0 Hours:0 Minutes:0 Seconds:0 0 : 0 : 0 : 0 0:00:00
TASK-2 01/14/2022 19:18:25 01/14/2022 20:20:06 Days:0 Hours:1 Minutes:1 Seconds:41 0 : 1 : 1 : 41 0:00:00
TASK-3 01/14/2022 20:20:06 01/21/2022 15:54:47 Days:6 Hours:19 Minutes:34 Seconds:41 6 : 19 : 34 : 41 47:54:47
TASK-4 01/21/2022 15:54:47 01/21/2022 16:21:24 Days:0 Hours:0 Minutes:26 Seconds:37 0 : 0 : 26 : 37 0:26:37
TASK-5 01/21/2022 16:21:24 01/21/2022 17:25:28 Days:0 Hours:1 Minutes:4 Seconds:4 0 : 1 : 4 : 4 1:04:04

0:00:00,0:00,00,47:54:47,0:26:37,1:04:04 -> this the outcome of the formula used (NETWORKDAYS(AA77018,AB77018)-1)*("18:00:00"-"8:00:00")+IF(NETWORKDAYS(AB77018,AB77018),MEDIAN(MOD(AB77018,1),"18:00:00","8:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS(AA77018,AA77018)*MOD(AA77018,1),"18:00:00","8:00:00")

AA... is my Task Arrival Date timestamp, AB... is my Task closer Date timestamp.
i am struggling with extracting days from this. if I work 10 hours a day then 47:54:47 should show me as 4 days 7 hours 54 minutes 47 seconds isn't it?
This comment was minimized by the moderator on the site
This formula is very good. Does anyone know how I can convert this to SQL query?
This comment was minimized by the moderator on the site
it works
how to add lunch break?
This comment was minimized by the moderator on the site
This is very good, what if the shift time spans over 2 days (start time 17:00 to 02:00 next day)
This comment was minimized by the moderator on the site
Hi
Have recieved any update regarding for your questions because I am also finding for same
This comment was minimized by the moderator on the site
Can anyone help me how the formula would be if the work hours are from 8:00 pm to 5:00 am (20:00 to 5:00)?
This comment was minimized by the moderator on the site
Tried the same formula but it's showing negative values.
This comment was minimized by the moderator on the site
Anyone need this formula but for graveyard or night shift schedule?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations