WORKDAY Function in Excel (Table of Contents)
WORKDAY in Excel
Workday function in excel returns the Date, which is the official working day from the date which we feed into the syntax. This is quite useful for getting what would the working day date after selective day counts. As per syntax, we just need to select the date from which we need to count the number of the working day, then select how many days we need to count, and if there is any week off, we have optional. If we select today’s date with 5 days and 2 weeks off days, we will get the date of the same weekday.
WORKDAY Formula in Excel:
Below is the WORKDAY Formula in Excel.
Explanation of WORKDAY Formula in Excel
A WORKDAY Function in Excel includes two mandatory parameters and one optional parameter.
- Start_date: “Starting date of the project or any work”.
- Days: The total number of days required to complete the work or project. This does not include weekends (Saturday and Sunday).
- [Holidays]: This is an optional parameter. This section asks whether the days you have mentioned include any holidays. For this, you need to make a list of holidays separately.
WORKDAY Function in Excel by default excludes Saturday and Sunday as weekend days. If at all you need weekends for any other day, you can use WORKDAY.INTL function. For example: In the Middle East region, weekend days are Friday & Saturday. In these cases, we can use WORKDAY.INTL function instead of a normal WORKDAY Function in Excel.
How to Use WORKDAY Function in Excel?
WORKDAY Function in Excel is straightforward and easy to use. Let us understand the working of WORKDAY Function in Excel by some WORKDAY Formula examples.
Example #1
Using the WORKDAY Function in excel, we can generate a series of dates even though we can generate by using the drag and drop option.
Step 1: Enter the one date on cell A2 as 12/Nov/2018.
Step 2: Now, in cell A3, apply the WORKDAY Function as shown in the below image.
=WORKDAY(A2,1)
The above formula takes the cell A2 as a reference and increases the date by 1.
Step 3: Drag the formula until cell A18.
Look at the formula here; 12/Nov/2018 is on Monday; we are increasing the day by 1. When we drag the formula, it will increment the date by 1 until 16/Nov/2018. If you drag one more time, it will jump to 19/Nov/2018 and excludes 17/Nov/2018 and 18/Nov/2018; those are weekends.
Similarly, in the next week, workdays are from 19/Nov/2018 to 23/Nov/2018, and weekends are 24/Nov/2018 and 25/Nov/2018.
Example #2
The project starting date and project duration date calculate the project ending date by using a WORKDAY Function in Excel.
Step 1: Copy and paste the above data to an excel sheet.
Step 2: Apply the WORKDAY Function in column C starting from cell C2.
=WORKDAY(A2,B2)
Result is :
We can drag the formula by using Ctrl + D or double-click on the right corner of cell C2. So the result would be:
Example #3
Consider the above example data for this also. But here, the list of holidays is available to estimate the project ending date.
The list of holidays are:
Apply the same formula as shown in example 2, but here you need to add one more parameter, i.e. holidays.
=WORKDAY(A2,B2,$G$2:$G$21)
Result is :
We can drag the formula by using Ctrl + D or double-click on the right corner of cell C2. So the result would be:
Example 2 vs Example 3:
Now we will see the difference between the two examples.
In the second example for the first project-ending date is 8/8/2017, and there is one holiday, for example, 3, so the ending date increased by 1 day.
For the second project, the ending date is 30/01/2018, for example, 2 and example 3; there 5 holidays, so the ending date increased by 7 days because of the in-between weekend.
Example #4
Assume you are working in the Accounts Receivable team; you have a list of invoices and due dates against those invoices. You need to find the due days for those invoices.
Apply a WORKDAY Function in Excel to get the exact due day.
=WORKDAY(A2,B2)
Result is :
We can drag the formula by using Ctrl + D or double-click on the right corner of cell C2. So the result would be:
Initially, the result looks like serial numbers. We need to change our formatting to make it correct.
Step 1: Select the entire range.
Step 2: Now press ctrl +1. It will open up a formatting dialogue box.
Step 3: Select custom and apply the format as shown in the below image and click ok.
Step 4: Your result looks like the below one.
Things to Remember
- If you want to use different weekends other than Saturday and Sunday, use WORKDAY.INTL function.
- We can use only numeric values for the day’s argument.
- The date and days should be accurate otherwise;, we will get the error as #VALUE!
- If the date includes time, then the formula considers only the date portion and ignores the time portion.
- If you supply decimal numbers, a formula will round down the value. For example: if you supply 10.6 days, then the formula treats this as 10 days only.
Recommended Articles
This has been a guide to WORKDAY ID in Excel. Here we discuss the WORKDAY Formula in Excel and how to use the WORKDAY Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –
