Log in  \/ 
x
or
x
Register  \/ 
x

or

How to add number of business/working days or hours to a date in Excel?

You may need to add a number of business days or hours to a date in order to know the exact time you will finish the task during the working time. In this article, we will show you methods of adding number of business/working days or hours to a date in Excel.

Add number of business days to a date with formula

Add number of business hours to a date with formula

Add number of business days to a date with formula

Supposing date 2016/1/5 locates on cell A2, if you want to add 12 days only including working days to it without weekends, please do as follows.

1. Select a blank cell, enter formula =WORKDAY(A2,12) into the Formula Bar, and then press the Enter key. See screenshot:

Then you will get the date after adding 12 working days.

Notes:

1. In the formula, A2 is the cell contains the date you want to add working days to, 12 is the number of working days you will add to the date. Please change them as you need.

2. With the above formula, you will get the result excluding weekends. But it may include some holidays after calculating. If you want to exclude both weekends and holidays, please apply this formula =WORKDAY(A2,B2,C2).

doc add working hours days to a date 1

In this formula, A2 contains the date you will add working days to, B2 contains the number of working days, and C2 locates the date of holiday.

3. After applying the formulas, if you get a 5-digits number, please convert the cell format to date format.

Add number of business hours to a date with formula

Supposing you have a start date and time, number of working hours needed to add, the start and end times of your work hours and the holiday you want to exclude as below screenshot shown. For adding number of business hours to the date, please do as follows.

doc add working hours days to a date 1

1. Select a blank cell (says cell C2), enter the following formula into the formula bar, and then press the Enter key.

=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))

Then you can see the result as below screenshot shown.

doc add working hours days to a date 1

Notes:

1. In the formula, A2 is the cell contains the date, B2 contains the working hours you will add to date, E2 and F2 are the start and end time of your work hours, and $G$2 is the specific holiday date. You can change them based on your needs.

2. If you get a number after applying this formula, please change the cell format to a date and time format.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
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.
    harish · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Harish,
      Sorry can't help you with that yet. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Hash Ann · 1 years ago
    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.?
  • To post as a guest, your comment is unpublished.
    tiit.perve@gmail.com · 1 years ago
    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?
  • To post as a guest, your comment is unpublished.
    SHAHID · 2 years ago
    IF WE HAVE TO WRITE THE SAME FORMULA WITH WORKDAY.INTL THAN HOW TO WRITE IT
  • To post as a guest, your comment is unpublished.
    agnese.koduvere@gmail.com · 2 years ago
    Please, help... this formula works perfectly if start time is between the fixed start and end times, but if the start time happens to be outside the "legal work hours", it calculates wrong end date/time, because it ads "before schedule" hours and this way the calculated end time is earlier than it should be. I have a file, where the start date/time is not manually set (imported from another list) and it can be any day and any (exact) hour. What I want is that no matter what is the start date/time, excel would still add only net work hours that are in the schedule (in my case 9:00 - 17:00).
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Agnese,
      Sorry can’t help with this, you can post your question to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
  • To post as a guest, your comment is unpublished.
    lucanpa@gmail.com · 3 years ago
    How do i do this without business hours.
    In fact, i want to sum minutes to a date, and results me the date with hours and minutes.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good day,
      If you want to add minutes to cell such as 500 minutes, please try this formula =A1+500/1440.
      • To post as a guest, your comment is unpublished.
        lucanpa@gmail.com · 3 years ago
        Hi Crystal,
        Thank you for the answer, i solve my problem configuraing the "begin" and the "end" with the values "00:00" and "23:59".
        To get minutes between two dates.

        To add the minutes i elaborate other formula:
        DataCorteHora = 1/29/18 9:00 AM
        Tempo Restante = minutes
        IF(AND(WEEKDAY(DataCorteHora+[@[Tempo Restante]],11)=5,SUMPRODUCT((TblFeriado[Data]>=DataCorteHora)*(TblFeriado[Data]<=DataCorteHora+[@[Tempo Restante]]))>0),DataCorteHora+[@[Tempo Restante]]+3,
        IF(AND(WEEKDAY(DataCorteHora,11)=5,[@[Tempo Restante]]>0.625),DataCorteHora+[@[Tempo Restante]]+2,
        IF(AND(WEEKDAY(DataCorteHora,11)=6,[@[Tempo Restante]]>0.625),DataCorteHora+[@[Tempo Restante]]+1,
        IF(SUMPRODUCT((TblFeriado[Data]>=DataCorteHora)*(TblFeriado[Data]<=DataCorteHora+[@[Tempo Restante]]))>0,DataCorteHora+[@[Tempo Restante]]+1,DataCorteHora+[@[Tempo Restante]])))))

        This formula sum my period in minutes and validate it if the final date is a holiday, weekend, etc.

        "today() + minutes = {holiday, weekend, etc.}"
  • To post as a guest, your comment is unpublished.
    001djk@gmail.com · 3 years ago
    What if the workday shift is other than 8 hours ? I appear to be having errors if my workday shift is more than 8 hours
  • To post as a guest, your comment is unpublished.
    Swapnil Wale · 3 years ago
    This is life saver. Great work.