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

To determine the precise time at which you will have completed the assignment while you are actively employed, you may need to add a certain number of business days or hours to the given date. In this article, we will demonstrate many different approaches to including a number of business days or working hours into a date using Excel.

Assuming that the date 01-01-2022 is located in cell A2, the steps that need to be taken in order to add 22 days to it that only include working days and not weekends are as follows −

The formula for calculating the number of business/working days −

=WORKDAY(Start_date, days) 

Where,

• Start_date − There are three possible outcomes for the relationship between Start Date and End Date, Start Date is earlier than End Date, later than the End Date, or the same as the End Date.

• days − In the event that you want to increase the total by "n" number of days, but only those that are considered to be "working days" and exclude weekends,

Step 1

This start date will serve as the basis for the computation going forward.

Step 2

After you have chosen a cell that is blank and entered the formula =WORKDAY(A2,22) into the Formula Bar, you should then hit the Enter key. View this screenshot −

Step 3

The date will be obtained for you after adding 22 business days to the current date.

In the formula, the cell that holds the date to which you want to add working days is denoted by the letter A2, and the number 22 denotes the total number of working days that will be appended to the date. Make the necessary changes.

Excluding Holiday

The outcome will be calculated without taking into account weekends if you use the formula given above. However, after the calculations are completed, holidays could be considered for. Please use this formula instead of others if you wish to exclude weekdays in addition to weekends and holidays.

=WORKDAY(start_date, days, [holidays]) 

Where,

• Start_date − the date from which the number of business/working will begin to be counted.

• Days − The number of business days that should be added to start date after the initial countdown has finished.

• Holidays − A list of dates that are optionally excluded from working day calculations. This may either be an array constant of the serial numbers indicating the dates or a range of cells holding the dates you want to omit from computations.

Step 1

Let's imagine you want to find out the dates 22 workdays in the future and 22 workdays in the past, and you have a start date in cell A2 of a spreadsheet and a list of holidays in columns C2:C3 of the same spreadsheet. How would you go about doing this? You will find that the equations that follow are quite helpful during this procedure.

Step 2

After you have chosen a cell that is blank and entered the formula =WORKDAY(A2,B2,C2:C3) into the Formula Bar, you should then hit the Enter key. Refer the following image −

Step 3

After adding 22 regular business days (excluding weekends and holidays) to the current date, we will calculate the date for you.

Add Number of Business/Working Days or Hours to a Date

Suppose you already know the start date and time, the amount of additional working hours that need to be added, the beginning and ending times of your work hours, and the holiday that you do not want to be included as part of the total, as seen in the picture below. Please follow these instructions in order to add the number of business hours to the date.

When you add two times together, you will obtain a date and time in the future that occurs within working hours. Weekends and holidays are not included in this calculation. I'm going to demonstrate how to do this task by utilizing a formula.

=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),$C$2:$C$3)+I
F(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))

Step 1

If, after using this formula, you get a number, please ensure that the format of the cell is set to a date and time format.

Here,

• Start Date/Time − This marks the beginning of the calculation both in terms of date and time. The time and date ought to be entered into the same box.

• Hours − This is the amount of time that should be added to the date and time of the commencement. This is NOT how a date should be represented at all. It must be a number that repeats itself regularly. Addition of minutes is accomplished by writing them in "fraction-of-an-hour" format; for example, 28 hours and 30 minutes would become 28.5 when converted to this format.

• Holiday − A rundown of any and all holidays. Every holiday should be entered as a date using the appropriate format for dates. In the event that this is not required, you are free to leave it blank.

• Start Time − The starting time of each working day.

• End Time − The ending time of each working day.

Step 2

In the formula, the cell that contains the date is denoted by the letter A2, the cell that contains the working hours that you will add to the date is denoted by the letter B2, E2 and F2 are the beginning and ending times of your work hours, and the cell that contains the specific holiday date is denoted by the letter $C$2: $C$3. You are free to alter them according to your requirements.

Choose a cell that is empty (let's say cell F2), copy and paste the formula into the blank space in the formula bar, and then hit the Enter key.

Where,

• A2 − Start Date/Time

• B2 − the number of additional hours to be added to the date and time

• C2:C3 − A list of all of the holidays.

• D2 − The beginning of each working day.

• E2 − Whenever the working day is over.

Step 3

The outcome may be seen in the cell labelled F2.

Conclusion

In this tutorial, we explained how you can add the number of business/working days or hours to a date in Excel.