Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating Excel date difference of week days only

Posted on 2007-04-10
10
Medium Priority
?
238 Views
Last Modified: 2010-05-18
I'm looking to make an Excel formula that takes the difference of two dates, skipping weekends.  I want only weekdays, but the standard subtraction formula counts all calendar days.

How can i do this without installing a third-party add-in?  THanks
0
Comment
Question by:bdietz
[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
  • 3
  • 3
10 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 18885539
The formula below returns the number of specific week days between any two dates. The week days to count are specified as an array enclosed in braces. The week days are integers where 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, and 7 = Saturday. The example formula below counts the number of week days (Monday through Friday) between the two dates.

   =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={2,3,4,5,6})*1)

The example formula below counts all Mondays between the dates in A1 and B1.

   =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)*1)

The two dates do not have to be in any order. If only one week day is specified the braces are not required. Dates that can be used range from January 1, 1900 to June 5, 2079.

To consider holidays, use the formula template below.

   =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={2,3,4,5})*(COUNTIF(X1:X10,ROW(INDIRECT(A1&":"&B1)))=0))

Where the range X1:X10 is a list of holidays specified as Excel date time values. Only week days that do not fall on the specified list of holidays are counted.

Kevin
0
 
LVL 5

Expert Comment

by:Michael V Bernot
ID: 18885758
There is also a function that requires the Analysis Toolpak add-in (Tools - Add-Ins check Analysis Toolpak) called NETWORKDAYS.  

=NETWORKDAYS(startDate, EndDate)

You can also add a comma after the end date and include the list of Holidays (range of cells) you would also like to exclude.  Good Luck.

MVB

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18885782
A word of caution: NETWORKDAYS requires a reference to the Analysis ToolPak.

Kevin
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:bdietz
ID: 18890370
Thanks.

For formula

 =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)*1)

Works if cells A1 and B1 contains dates.  However, follow up:  I want to compare cell A1 to the current date.  I tried NOW() in place of B1 and i get a #REF!.  I also tried DATEVALUE(NOW()) and i get #VALUE!.

Can you help?  Thanks
0
 
LVL 5

Expert Comment

by:Michael V Bernot
ID: 18890390
Bdietz,

Try today().

MVB
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18892165
Note that the formula you chose only counts Mondays. If you want to count week days (Mon-Fri) then use:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)))={2,3,4,5,6})*1)

The formula requires integer values for the dates (no time component) so you can truncate the value:

TRUNC(B1)

you can truncate the NOW function result:

TRUNC(NOW)

or you can use the TODAY function as MVB stated.

Excel stores dates as double values where the whole part of the number of the day and the fractional part is the time.

Kevin
0
 
LVL 5

Expert Comment

by:Michael V Bernot
ID: 19053538
Daleoran,
    Kevin's solution is one that I've added to my KNOWLEDGE BASE.  It's a unique way to use the INDIRECT function to turn the date input into a range!  It does not require the ANALYSIS TOOL-PAK, but is a little more involved than the built in NETWORKDAYS function that can be accessed with the ADD-IN.
    Both solutions do provide a different way to arrive at the same answer to the question.  That being said, any recommedation you make would be fine with me!

Michael

   
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 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