Solved

Date Difference/Subtracting With Weekends (Network Days)

Posted on 2008-10-29
2
1,012 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamic Excel Input Form 29 32
Excel calculate based on 'x' in column 2 22
Unhide very hidden sheets with password 22 44
sort column using vba 2 15
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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