Excel 2010: Calculate Working Days In Date Format

Excel provides loads of functions and formulas which brings down the level of complexity of daily-life problems. Counting business days (working days) manually would be a tiresome job to get through but Excel 2010 offers an excellent function which will let you to find out the specified working days promptly. What it does is to automatically exclude the specified holidays, weekends, etc and yields the exact result in Date format.

To start off with, launch Excel 2010 spreadsheet and start creating table having fields; Working days and Holidays, we have also created two labels namely; Today and After Working Days.

image

Now we need to find out the working days exclusively, after adding number of days and by excluding holidays and weekends. We will be finding it out with the help of a WORKDAY.INTL function. The syntax of the function is;

=WORKDAY.INTL(start_date,days,[weekends],[holidays])

The first argument is start_date, which refers to the date from which you would like to start the calculation. The second one is days, referring to number of days for which you would like to count. While filling in the third argument Excel will automatically provide you with a list of options. Showing different weekends options and if you want to include some extra holidays you can do so by filling in last argument which is holidays.

As you can see from screenshot above, that we have already included holidays and working days. Now we need to mention today date, for this you can use today() function, or press Ctrl+; (semicolon) to insert today’s date quickly.

today

Now we start writing function as;

=WORKDAY.INTL(D2,A2,1,B2:B4)

For weekends it will show you the list, just enter the respective number for inserting weekends.

saturday


In function argument D2 is the location of cell having today’s’ date, A2 is the cell’s location where specified working day is residing. 1 in the function argument is weekend option chosen from the list, and B2:B4 is range of holidays.

It will yield the result as a number , which doesn’t make any sense.

number

To get it over with, navigate to Home tab and from Number group, choose the desired Date format.

date

Upon click it will yield the result in date format. Actually the function calculates future working date, by considering gap of days which is 10, and by excluding weekends and holidays. To make it more simpler this function would calculate the exact date for specified business days.

date result

If you want to find out all the dates for working(business) days, then change the holidays date & today date argument values with absolute referencing (insert dollar sign)

=WORKDAY.INTL($D$2,A2,1,$B$2:$B$4)

Now just drag the plus sign at the end of column to apply it over. Verify the last evaluated date to verify the whole list.

working days 1

You can also check out our previously reviewed Excel functions; LARGE, CEILING, RANK, TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

Advertisement