Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Date Difference/Subtracting With Weekends (Network Days)

Posted on 2008-10-29
2
1,020 Views
Last Modified: 2009-01-11
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
Comment
Question by:Sk1lly
2 Comments
 
LVL 20

Expert Comment

by:pari123
ID: 22832931
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 125 total points
ID: 22835661
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question