Many times while working in workflow management, as a team leader, manager or just as a student, you will get the need to know that if a given day is workday or a holiday. In Excel, we can easily find out if a given day is Weekday/workday or weekend/holiday.
To find out if a given date is workday or not, we will use the WORKDAY function. The syntax of this excel formula is:
| =WORKDAY(date-1,1,[holiday dates]) = date |
Date: It is the date that you want to check if it is a business day or a holiday.
[Holiday dates]: It is optional. It is the list of dates that are listed as holidays other than weekends.
So now we have the syntax. Let see its effect in an example.
Here, we have some list of dates. We need to check if the given dates are holiday or workday.
Write this formula in the cell C3 and drag it down:
| =WORKDAY(B3-1,1,$B$10)=B3 |
And this returns the result as shown in the picture below:
All TRUE containing cells are telling that the given date is a workday. All the FALSE containing cells are telling that the given date is a holiday.
How Does it Work?
The WORKDAY function returns the workday after a given number of days from a start date. You can read about it here in detail.
Let's see the formula in cell C7. It is =WORKDAY(B7-1,1,$B$10)=B7
Now B7-1 returns a date prior to B7 which becomes Sunday, March 1, 2020. Now the formula is =WORKDAY(3/1/20,1,$B$10)=B7.
Next, the formula check adds 1 to the date as we want to add 1 workday to 3/1/20 (WORKDAY(3/1/20,1,$B$10). Now first it will return Monday, March 2, 2020. But it is a holiday as we have stated in cel B1o. So it returns Tuesday, March 3, 2020.
Finally, we are checking the date return by workday which is Tuesday, March 3, 2020, is equal to date in B7. Which is clearly not and returns falls.
The Logic of The Formula:
The logic of the formula is to get the next working date from the date prior to the given date, and then check if this date is equal to the date we want to check, or not. If it returns TRUE, it means it is a business day else it is not.
If you want, you can get a more sophisticated answer like WORKDAY or Holiday instead of TRUE or FALSE.
| =IF(WORKDAY(B3-1,1,$B$10)=B3,"Workday","Holiday") |
It will print "Workday" if the date is a workday else it will print "Holiday".
So yeah guys, this is how you can know if a given date is a workday or not in excel. I hope it was explanatory. Follow the links in the formulas to know more about the function that are used in this formula. If you have any doubts regarding this topic or any other topic of excel, ask in the comments section below.
Keep Excelling.
Related Articles
How to Add Business Days in Excel | To add business days to a date in a given day while taking the weekends and holidays in consideration
How to use the EDATE function in Excel |EDATE function returns the date after adding or subtracting months on a given date. The EDATE function takes the date as an argument and edits n months to the date.
Calculate age from date of birth | To calculate age we use the TODAY function of excel. We subtract the date of birth from today's date.
How to Group Data By Age Range in Excel | To group data by age in the pivot, follow the simple steps explained here. All the data will be arranged in a mannered way.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
