提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何计算两个日期之间的净工作时间(不包括Excel中的周末或假期)?

在许多公司中,工作人员按工作时间收费。 计算一天中的净工作时间很简单,但是如何计算日期范围内的净时数? 为此,本文介绍了计算Excel中两个日期(不包括周末和假日)之间的净工作时间的公式。

计算工作日排除周末

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


计数两个日期之间的平日/周末/特定平日

假设你有两个给定的日期,一个是开始日期,另一个是结束日期,你如何快速计算工作日,周末或某个特定的工作日,如星期一在这个日期范围之间? 如果你感兴趣,你可以尝试 Kutools for Excel's 计算平日/周末/特定工作日 公用事业。
doc计算周末周末
Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

箭头蓝色右泡 计算工作日排除周末


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

1。 选择您将输入开始日期时间和结束日期时间的两个单元格,然后单击鼠标右键进行选择 单元格格式 形成上下文菜单。 看截图:
doc净工作时间1

2。 在里面 单元格格式 对话框中,单击 选项卡,然后选择 私人订制 形成 类别 列表并输入 m / d / yyyy h:mm 类型 在右侧的文本框。 看截图:
doc净工作时间2

3。 点击 OK。 并将开始日期时间和结束日期时间分别输入到两个单元格中。 看截图:
doc净工作时间3

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


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

如果你想计算不包括周末或节假日的净工作时间,你可以这样做:

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

Kutools for Excel, 与超过 120 方便的Excel功能,提高工作效率,节省工作时间。

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"),
输入 键,然后你得到一个数字字符串。 看截图:
doc净工作时间7

3。 右键单击数字字符串,然后单击 单元格格式 从上下文菜单和中 单元格格式 对话框中选择 私人订制 形成 类别 列表下 Number标签,然后输入 并[h]:毫米 到类型文本框中。 看截图:
doc净工作时间8

4。 点击 OK。 现在计算两个日期之间的净工作时间,不包括周末。
doc净工作时间9

小技巧:在公式中,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

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

doc下载1


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    CL · 1 months ago
    PLEASE NOTE that the work day and time formula does NOT WORK if the time of the start value (i.e. 11am) is greater than the time in the second value (i.e. 9am). It will give a negative value which will deduct these hours from the work time. To capture that properly, you need to add another condition to the formula. IF(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") <0, TIME("17:30"-"8:30",0,0)+(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"))),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"))))
  • To post as a guest, your comment is unpublished.
    Chad · 3 months ago
    I am getting incorrect values when I enter in this equation, trying to look at open and close times, same as work hours.
  • To post as a guest, your comment is unpublished.
    karthik · 3 months ago
    Ho to calculate time difference between date and time from below date and time excluding weekends.
    Start date 12/31/2022 9:04 End Date 1/3/2023 8:07 in HH:MM:SS format.
  • To post as a guest, your comment is unpublished.
    George · 4 months ago
    Works greet in excel.. trying to replicated the same logic in Power BI but having issues. Any body has an idea?
  • To post as a guest, your comment is unpublished.
    BasilBase · 8 months ago
    I have edited your formula for work hours being 8am to 8pm. In my example, A2 is the start date time, B2 is the end date time, 8:00 and 20:00 are the start and end times each day, H$1:H$8 is the holiday cells.

    When the date range does not include a weekend, the formula works perfectly, but when the date range includes a weekend day, the result is always 12:00 greater than it should be

    The formula I'm using is =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$8)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$8),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$8)*MOD(A2,1),"8:00","20:00")

    Here are 2 examples (there aren't any holidays (H$1:H$8) in these date ranges)
    Example 1
    Start - 2/6/2019 8:46
    End - 2/11/2019 8:40
    Value Using Formula - 47:54:00
    Correct Value - 35:54:00
    Difference - 12:00
    note date range has weekend date in it

    Example 2
    Start - 2/6/2019 19:26
    End - 2/8/2019 16:15
    Value Using Formula - 20:49
    Correct Value - 20:49
    Difference - 00:00
    note date range has no weekend dates in it

    Thanks for any help you can give!
    • To post as a guest, your comment is unpublished.
      RB · 7 months ago
      Had same issue, but with help of feedback 2 months ok of AydinB was able to fix it.

      Formula includes Saturday. Number 11 in
      Means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)
  • To post as a guest, your comment is unpublished.
    blessing · 8 months ago
    hello i need help in calculating working hours in a day within some period of time. take for instance:

    work hours is 8am to 5pm. and i need to calculate this for 10 days. what formula can i use
  • To post as a guest, your comment is unpublished.
    AydinB · 10 months ago
    Formula includes Saturday. Number 11 in

    NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)

    means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)

    =(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"),
  • To post as a guest, your comment is unpublished.
    J.M. · 10 months ago
    Hello

    I used your formula "=(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")" works great

    But i get with this data negative Errors even using "1904 date system".

    The sample data is: 01.10.2018 15:10 / 03.10.2018 11:15 (date format t.m.jjjj hh:mm)

    The error is: data and times that are negative or too large show as #######

    Do you have an idea.

    Juan
  • To post as a guest, your comment is unpublished.
    Pedro · 11 months ago
    Hi everyone,


    There is an error on this formula:

    =(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"),


    If the end time is greater than the start time, the formula give us the wrong value. We have to pay attention to that.


    But the formula was very helpful! Thanks
    • To post as a guest, your comment is unpublished.
      Ranjith Kumar Reddy · 4 months ago
      Hello Sir,
      Even I am finding the same error as you mentioned above, could you please share the answer if you have

      Deva
    • To post as a guest, your comment is unpublished.
      Alex Hart · 8 months ago
      then it is not an end time :)
  • To post as a guest, your comment is unpublished.
    Cherry · 11 months ago
    what is "11" in the formula --> (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"),
  • To post as a guest, your comment is unpublished.
    Jack · 1 years ago
    Morning,

    I4 = Date Start MFG (9/14/18 8:00)
    J4:M4 = Estimated Hours of Work (28)
    N4 = Sum(J4:M4)/8 with the 8 representing the hours of work to calculate the # of days required
    O4 = MFG Lead-Time.. this is where I'm having an issue..

    What I want the spreadsheet to do is to tell me when the job is going to finish; more specifically, the time. However, I'm not sure how to write the formula so it only counts 7:00-17:00 and excludes 17:00-7:00.


    Right now, I have a 3.5 day LT beginning at 9/14/18 8:00 and the output is giving me 09/17/18 20:00. But I can't have a 20:00 because it's outside the standard hours of work. The desired result should be 09/17/18 12:00.

    Start Date: 09/14/18 8:00AM - 17:00PM is 1, 9/15/18 8:00AM - 17:00PM is 2, 9/16/18 8:00AM - 17:00PM is 3, 9/17/18 8:00AM - 12:00PM is 3.5.. any ideas?
  • To post as a guest, your comment is unpublished.
    narender kumar · 1 years ago
    what if weekend is only sunday
    • To post as a guest, your comment is unpublished.
      mahadev · 1 years ago
      use the formula =NETWORKDAYS.INTL you will get all the syntax over there for your query
  • To post as a guest, your comment is unpublished.
    Sebastian · 1 years ago
    Hi, I'm trying to find the formula that include the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. is it posted? thank you so much for your help, this Blog has helped me a lot!
    • To post as a guest, your comment is unpublished.
      Sandra · 1 years ago
      Any luck with this? I am also looking for a formula to subtract one hour per day, but not necessarily a set time. In my setting, the work day is 8-5, but only 8 hours are counted. Thanks!
  • To post as a guest, your comment is unpublished.
    Nawab · 1 years ago
    hi, i need to compare a login time if its in between a range of time in case if its fall time of range 1 then authorization time of same login is within specific time of range 1 and we have 3 to 4 range of different timings and its authorization respectively?
    can any one help?
  • To post as a guest, your comment is unpublished.
    kara m · 1 years ago
    This formula works to return the net working hours and minutes. How can I convert hours to days, based on an eight hour work day? For example, the result "0 day, 18 hours, 45 minutes" should be "2 days, 2 hours, 45 minutes"
    • To post as a guest, your comment is unpublished.
      deb · 1 years ago
      divide the hours by 8 instead of 24 as working hours is 8hrs for your team. so 18 hrs 45 mins/ 8 hrs= 2 days 2 hrs 45 mins (2 days = 2*8 = 16hrs)
  • To post as a guest, your comment is unpublished.
    Yesu · 1 years ago
    Hi, for me some values for start/end time falls on weekends or time outside the defined window. For these cells, the value is showing as 00:00:00. Is there a way to correct it?
  • To post as a guest, your comment is unpublished.
    Jenny Cruz · 2 years ago
    Hi there, Thank you very much your formula it really helps me a lot with my work. but my challenge is how can you removed the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. It really means a lot to me if you will solved my problem. please help..
    • To post as a guest, your comment is unpublished.
      Sebastian · 1 years ago
      Hi, do you know where could I find the formula that includes the break you are mentioning? Thank you !
  • To post as a guest, your comment is unpublished.
    vishnu k · 2 years ago
    =(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"),


    could you please explain how this works.
  • To post as a guest, your comment is unpublished.
    Rahul · 2 years ago
    =(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"),

    What is number 11 in the above formula.??

    And also, How to write the holiday in formula if I have only one holiday in the month??
    • To post as a guest, your comment is unpublished.
      manohar · 2 years ago
      Number 11 (Sunday as weekend) refers to weekend number
      Weekend number Weekend days
      1 or omitted Saturday, Sunday
      2 Sunday, Monday
      3 Monday, Tuesday
      4 Tuesday, Wednesday
      5 Wednesday, Thursday
      6 Thursday, Friday
      7 Friday, Saturday
      11 Sunday only
      12 Monday only
      13 Tuesday only
      14 Wednesday only
      15 Thursday only
      16 Friday only
      17 Saturday only
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        Hi,

        pls post how to calculate the time b/w two dates which includes weekends also.
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        hi ,

        Could you please write the formula for calculating time b/w days which includes weekends ( sat n sun )
      • To post as a guest, your comment is unpublished.
        appa · 1 years ago
        Hi ,

        Can you please share the formula which calculates time including weekends(sat & Sun) also.
  • To post as a guest, your comment is unpublished.
    KMYounis · 2 years ago
    Hi,
    I have been trying to use this function to get minutes between two days excluding holidays and weekends. No matter what I always get 0 minutes in my answer. I will be more than happy to share my excel file if needed.

    Your help will be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Thanks for ur message. For get minutes between two dates excluding holidays and weekends, you just need to change [h]:mm to [mm] in the last step
  • To post as a guest, your comment is unpublished.
    Claire · 2 years ago
    Thanks, in general this formula is working great for me thank you but how do I do it for time periods which exceed a month?

    My formula is this:

    =(NETWORKDAYS(L22,M22,BankHols17to21)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(M22,M22,BankHols17to21),MEDIAN(MOD(M22,1),"18:00","8:00"),"18:00")-MEDIAN(NETWORKDAYS(L22,L22)*MOD(L22,1),"18:00","8:00")

    So these two dates show correctly as 11 working hours:

    18/05/2017 08:00 AM 19/05/17 09:00 AM

    But this one which is over a year difference between the dates shows ups 17 days 8 hours:

    18/05/17 00:00 28/05/18 09:00

    Thank you.
    • To post as a guest, your comment is unpublished.
      Z. · 1 years ago
      I have the same problem.
      Did you get it solved?
  • To post as a guest, your comment is unpublished.
    Ankit · 2 years ago
    Hi,

    Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00). Please help.
  • To post as a guest, your comment is unpublished.
    Ankit · 2 years ago
    Hi,

    Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00).
  • To post as a guest, your comment is unpublished.
    Adis Samardzic · 2 years ago
    Hello I need help on same topic

    In my case I have defined starting date and time (dd.mm.yy and hh.mm) and I have hours needed for some material to be produced (ex total 17 hours)

    My problem is how to subtract non working hours from total time needed third shift is not working from 0:00 AM - 7:00AM

    Please help
  • To post as a guest, your comment is unpublished.
    Steve · 2 years ago
    Really good info but can it be converted into minutes?

    Thanks
    Steve
    • To post as a guest, your comment is unpublished.
      Melinda · 2 years ago
      yes, format the result cell with custom format [mm] instead of [h]:mm
  • To post as a guest, your comment is unpublished.
    Deepinderpal · 2 years ago
    I have multiple dates I need to use to calculate the hrs spent on the iteration
    A - request received : 1/14/17 3:43
    B - request ended : 1/16/17 23:03
    C - clarification sent : 1/16/17 20:41
    D - clarification received : 1/16/17 22:38
    I need to find the difference B-A = E and then D-C = F
    now E-F should give me no. of hrs spend on this work which needs to be below 24 hrs