?
Solved

Date Difference/Subtracting With Weekends (Network Days)

Posted on 2008-10-29
2
Medium Priority
?
1,049 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
[X]
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
2 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
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 500 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

741 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