Is is easy in Excel to calculate the number of working days
between two dates. Most, but not all businesses, operations or project
activities and progress happen during weekdays. So, if you need to
report and calculate the number of days that have elapsed between a
start date and an end date of a project or project milestone for example
then counting weekends in the calculation is not what you want to do,
and you will need to avoid those days in your calculations. It is easy
to do in Excel with the NETWORKDAYS function.
The formula NETWORKDAYS is pretty straightforward and has two required arguments or parts to it.
The syntax of the formula is
=NETWORKDAYS(start_date,end_date)
So, an example always helps when working through Excel formulas.
Below is the start date and end date of a short project. Start Date is in cell C4 and End Date is in D4.
01/01/2015-Start Date
31/03/2105-End Date
The formula calculates the number of workdays (excluding Saturdays and Sundays which is the default), in this example it is 64 days.
So, this is a straightforward calculation automatically excluding Saturdays and Sundays, but some projects could and do include Saturdays, Sundays or even both.
Well of course Excel can handle this. In this instance we can use the NETWORKDAYS.INTL function.
The difference with this formula this is that it includes an extra argument or part, a weekend code, which allows us to specify which days to exclude as a weekend day or days. The syntax of this formula is
=NETWORKDAYS.INTL(start_date,end_date,weekend)
Let's apply the same formula- but let's assume we know our project work was active on Saturdays also. So, we need to ensure exclude any Saturdays from the calculation of days worked on our project
So, we need to select option 17 which is Saturday only. You can choose any of the options of 1 to 17 from the drop down menu. This now increases our work days to 77 days in the period 01/01/2015 to 31/03/2015 as Saturdays are now included as normal working days and should increase the number of days worked on our project. The number of days between the two dates now increases to 77 workdays.
The NEWTWORKDAYS and NETWORKDAYS.INTL are a useful couple of Functions to have in your Excel Tool Kit.
The formula NETWORKDAYS is pretty straightforward and has two required arguments or parts to it.
The syntax of the formula is
=NETWORKDAYS(start_date,end_date)
So, an example always helps when working through Excel formulas.
Below is the start date and end date of a short project. Start Date is in cell C4 and End Date is in D4.
01/01/2015-Start Date
31/03/2105-End Date
The formula calculates the number of workdays (excluding Saturdays and Sundays which is the default), in this example it is 64 days.
So, this is a straightforward calculation automatically excluding Saturdays and Sundays, but some projects could and do include Saturdays, Sundays or even both.
Well of course Excel can handle this. In this instance we can use the NETWORKDAYS.INTL function.
The difference with this formula this is that it includes an extra argument or part, a weekend code, which allows us to specify which days to exclude as a weekend day or days. The syntax of this formula is
=NETWORKDAYS.INTL(start_date,end_date,weekend)
Let's apply the same formula- but let's assume we know our project work was active on Saturdays also. So, we need to ensure exclude any Saturdays from the calculation of days worked on our project
So, we need to select option 17 which is Saturday only. You can choose any of the options of 1 to 17 from the drop down menu. This now increases our work days to 77 days in the period 01/01/2015 to 31/03/2015 as Saturdays are now included as normal working days and should increase the number of days worked on our project. The number of days between the two dates now increases to 77 workdays.
The NEWTWORKDAYS and NETWORKDAYS.INTL are a useful couple of Functions to have in your Excel Tool Kit.
BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com
a site that shares Excel tips and tricks with it's enthusiastic
members. To join in the discussion, where users are working smarter and
faster with Excel sign up for the FREE newsletter and as a bonus receive
a FREE EBook- 50 Top Tips and Tricks.
Aucun commentaire:
Enregistrer un commentaire