codewala

code it

Exclude weekends from planned dates in Excel

Learnt something new today while working with excel. Details are given below.

Task
To create a detailed project plan.

Problem
While working on creating project plan in excel had to constantly refer the calendar to avoid adding the weekend/holidays into the plan. Also this task become cumbersome when the plan gets changed/updated.

Solution
Use the in-build excel function WORKDAY

What do WORKDAY function do?
Use this function to calculate a past or future date based on a starting date and a specified number of days.
The function excludes weekends and holidays and can therefore be used to calculate end/delivery dates.

Syntax
=WORKDAY(StartDate,Days,Holidays)

Note
For the WORKDAY function to work, the “Analysis ToolPak” add-in needs to be installed and should be active. To activate the add-in, go to Developer tab –> click on the “Add-ins” button –> tick the “Analysis ToolPak” checkbox –> click ok.

Advertisements

July 30, 2012 Posted by | Excel, VBA | , | Leave a comment