跳到主要内容

如何在Excel中的日期中增加营业/工作天数或营业时间？

您可能需要在日期中添加多个工作日或工作时间，以了解在工作时间内完成任务的确切时间。 在本文中，我们将向您展示在Excel中向日期添加业务/工作天数或小时数的方法。

使用公式将工作日数添加到日期

使用公式将营业时间数添加到日期

使用公式将工作日数添加到日期

假设日期2016/1/5位于单元格A2上，如果您只想添加12天（包括工作日，不包括周末），请执行以下操作。

1.选择一个空白单元格，输入公式 = WORKDAY（A2,12） 到 配方栏，然后按 输入 键。 看截图：

然后，您将在添加12个工作日后获得日期。

:

1.在公式中，A2是包含要添加工作日的日期的单元格，12是要添加到该日期的工作日数。 请根据需要更改它们。

2.使用上面的公式，您将获得不包括周末在内的结果。 但计算后可能包括一些假期。 如果您想排除周末和节假日，请应用此公式 = WORKDAY（A2，B2，C2）.

doc将工作时间天数添加到日期1

在此公式中，A2包含要添加工作日的日期，B2包含工作日的数量，C2定位休假的日期。

3.应用公式后，如果您获得5位数字，请将单元格格式转换为日期格式。

使用公式将营业时间数添加到日期

假设您有一个开始日期和时间，需要添加的工作时间数，工作时间的开始和结束时间以及要排除的假期，如下面的屏幕截图所示。 要在该日期增加营业时间，请执行以下操作。

doc将工作时间天数添加到日期1

1.选择一个空白单元格（例如单元格C2），在公式栏中输入以下公式，然后按 输入 键。

=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0),$G$2:$G$2)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+ TIME（MOD（B2,8），MOD（MOD（B2,8），1）* 60,0））

然后您可以看到结果，如下所示。

doc将工作时间天数添加到日期1

:

1.在公式中，A2是包含日期的单元格，B2是您要添加到日期的工作时间，E2和F2是工作时间的开始和结束时间，$ G $ 2是特定的假期日期。 您可以根据需要进行更改。

2.如果在应用此公式后得到数字，请将单元格格式更改为日期和时间格式。

最佳办公生产力工具

🤖 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 (22)
No ratings yet. Be the first to rate!
TA548
TA548
This comment was minimized by the moderator on the site
I have copy and pasted the formula below from the Add Number Of Business Hours To A Date With Formula step above. What is the equivalent formula where you could start with an end date and work backwards to the start date (instead of starting with the start date to find the end date).

=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0),$G$2:$G$2)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
Reply
Report
0
0
Joshua Player
Joshua Player
This comment was minimized by the moderator on the site
This should be a simple one for you guys but I am working in a dealership and trying to create a simple formula that will automatically calculate how many working days we have in a month... example (6 days a week, because we work every Saturday but we are closed every Sunday). Thanks in advance for your help.
Reply
0
0
crystal
crystal     Joshua Player
This comment was minimized by the moderator on the site
Hi Joshua Player,
For example, you need to count the number of working days in semptember including every Saturday. You need to do as follows:
1. Enter the start date and the end date of September in different cells, such as the start date 9/1/2022 in B1 and the last date 9/30/2022 in B2.
2. Apply one of the following formulas.
If you don't need to exclude holidays, apply this formula and press the Enter key to get the result:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"dddd")<>"Sunday"))
If you need to exclude holidays (suppose the holiday dates listed in B3:C3), apply this formula and press the Ctrl + Shift + Enter keys to get the result
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"dddd")<>"Sunday"))-SUMPRODUCT(--IF((B3:C3>=B1)*(B3:C3<=B2),(TEXT(B3:C3,"dddd")<>"Sunday"), FALSE))
Reply
Report
0
0
Renato
Renato
This comment was minimized by the moderator on the site
Hello Everyone,

Add Number Of Business Hours To A Date With Formula

I am not an expert but it gives me an error, everytime I insert the formula on the C2 cell. I followed all the instructions.
I have already formated all cells to time and date but still get an error saying that the formula contains an error.
Can someone explain or upload or send me an excel file with the formula already prepared?
Thanks in advance
Reply
0
0
crystal
crystal     Renato
This comment was minimized by the moderator on the site
Hi Renato,
Can you provide a screenshot of the formula error you encountered? Here I upload an Excel workbook with the formula prepared. Please download it and have a try.
View Attachments (1 / 5)
Reply
Report
0
0
Renato
Renato     crystal
This comment was minimized by the moderator on the site
Hi Crystal,

Thank you so much! I have downloaded the excel workbook and now it works.
Thanks again!
Reply
0
0
S ABDUL JABBAR
S ABDUL JABBAR
This comment was minimized by the moderator on the site
Good afternoon Sir,

How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours.

example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir.
2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir.

In CCC to be completed time also same formula given sir.

Can u please help me with the formula sir?
Reply
0
0
saadshakil
saadshakil
This comment was minimized by the moderator on the site
How Can I add hours with working date+time. For E.g, This is the time 20/10/21 8:00am and I need to add hours with in working days and I need to add 4 hours(assume), and there are working hours between 08:00 to 05:00 and there are no holidays. How to add?
Reply
Report
0
0
crystal
crystal     saadshakil
This comment was minimized by the moderator on the site
Hi, Supposing your case as shown in the screenshot below, to get a future date and time that falls within specified working hours, please try this formula:<div data-tag="code">=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,$D$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$E$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
View Attachments (1 / 5)
Reply
Report
0
0
Caysen
Caysen
This comment was minimized by the moderator on the site
the formula adding work hours works mathwise, but it wont display the accurate time, just says 0:00. anyone know why?
Reply
0
0
harish
harish
This comment was minimized by the moderator on the site
can some help me to get a formula for below condition
I am unable to get the time , If i do =WORKDAY.INTL(A2,B2,1) ---Output is : Deal Date: 9/11/2018 0:00, Where the time is not coming up, As time should come with 18:34 PM

A2: Deal Date :9/7/2018 18:34 PM
B2: Days: 2
Output is :New Deal Date: 9/11/2018 0:00

Required Output is 9/11/2018 18:34 PM - Plz help me with the formula!
--------------------
9/7/2018 - Friday , I need to add 2 days excluding weekends, So if i add 2 days to 9/7/2018(Friday ) its Tuesday (9/11/2018) by removing the Sat & Sun.
Reply
0
0
crystal
crystal     harish
This comment was minimized by the moderator on the site
Hi Harish,
Sorry can't help you with that yet. Thanks for your comment.
Reply
Report
0
0
Hash Ann
Hash Ann
This comment was minimized by the moderator on the site
How to add a cell in the excel says that maximum hours per day (i.e., 8 hours) The time calculation to be bound by 8 hours per day. Please help me if we can have the spread sheet cap the calculation at 8.?
Reply
0
0
tiit.perve@gmail.com
This comment was minimized by the moderator on the site
How to get cells in excel formula (H6; H7; H8; H9)
Forma C2 = =WORKDAY(B2;MAX(ROUNDUP((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>$H$3;0;IF(MOD(B2;1)>=$H$5;(H3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);0);0);$J$4:$J$91)+$H$2+(IFERROR(CEILING(MOD((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>$H$3;0;IF(MOD(B2;1)>=$H$5;($H$3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24+(IFERROR(CEILING(MOD((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>H3;0;IF(MOD(B2;1)>=$H$5;($H$3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24>($H$4-$H$2)*24)*($H$5-$H$4)*24)/24

and C3 = =WORKDAY(B3;MAX(ROUNDUP((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>$H$3;0;IF(MOD(B3;1)>=$H$5;(H4-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);0);0);$J$4:$J$91)+$H$2+(IFERROR(CEILING(MOD((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>$H$3;0;IF(MOD(B3;1)>=$H$5;($H$3-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24+(IFERROR(CEILING(MOD((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>H4;0;IF(MOD(B3;1)>=$H$5;($H$3-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24>($H$4-$H$2)*24)*($H$5-$H$4)*24)/24
But the correct value is in D2 and D3. How to get it?
Reply
Report
0
0
SHAHID
SHAHID
This comment was minimized by the moderator on the site
IF WE HAVE TO WRITE THE SAME FORMULA WITH WORKDAY.INTL THAN HOW TO WRITE IT
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations