• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

Date Difference/Subtracting With Weekends (Network Days)

Hi,

On Excel I want to subtract a date from another date to give me number I.E. 29/10/2008 (D1) - 24/10/2008 (C1) = 5, but I want it to take into consideration weekends.

So as the 25th and 26th were a weekend then the answer should be 3.

Also the dates may only go over 1 weekend day and not both I.E. 26/10/2008 - 24/10/2008 should equal 1 with this formulae.

Thanks for the help

Steve
0
Sk1lly
Asked:
Sk1lly
1 Solution
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
Won't the NETWORKDAYS give you what you need?
Syntax:  NETWORKDAYS(start_date,end_date,holidays)
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
0
 
barry houdiniCommented:
NETWORKDAYS counts all weekdays in the range including the start date and the end date so the formula
=NETWORKDAYS(C1,D1)
will give a result of 4 for your example. Some people just subtract 1 from the result to get the desired result but this doesn't necessarily work in all cases, e.g. if your start or end dates are non-weekdays
Here's an alternative to NETWORKDAYS if you don't want to use Analysis ToolPak functions
=SUM(INT((WEEKDAY(C1-{2,3,4,5,6})+D1-C1)/7))
.....but it'll still give a result of 4!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now