How to Calculate Work Days

Problem: Each employee in our company should pass the probation period (30 work days). How to calculate the end of this period?

Solution & Explanation:

To solve this problem we can use WORKDAY or WORKDAY.INTL functions. Just need to specify a start date, number of work days (30) and list of holidays.

1. Enter the holidays for this year (this list in E2:E7).

2. Enter the formula =WORKDAY(B2,$G$2,$E$2:$E$7)in cell C2. Please note that the argument containing the holidays and days of trial should be an absolute reference with dollar signs.

3. Format the cell C2 formula as a date.

4. Copy the formula down for all employees (see figure below).

Starting from Excel 2010 you can use WORKDAY.INTL function.

WORKDAY.INTL figures out a date that represents the "nearest" working day N days in the past or future. Use a positive number as days for future dates and a negative number for past dates. This function is more robust than the WORKDAY function because it lets you customize which days of the week are considered weekends (for example only Sunday or Friday).

Possible string values for the [weekend] argument consist of a series of seven 0's and 1's which represent the seven weekdays, starting from Monday. Each 1 denotes a day that should be counted as a weekend and each 0 represents a working day. For example, above formula can write like

0001100	-	denotes Thursdays and Fridays counted as weekend days
0000111	-	denotes Fridays, Saturdays and Sundays counted as weekend days.

 The string "1111111" is not valid.