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.
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.
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).
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.
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.
1. Select a blank cell (says cell C2), enter the following formula into the formula bar, and then press the Enter key.
Then you can see the result as below screenshot shown.
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!