DAYS BETWEEN DATES

[Danny C]

How do you calculate days between dates BUT not count the SAT & SUN in that
range?

I know you have to change everything to days of the week, but how do you
delete (or not count) SAT & SUN's

 

[Ron Coderre]

With
A1: (startdate)
B1: (enddate)

If you have the Analysis ToolPak add-in installed
=NETWORKDAYS(A1,B1)

or...if you don't....
=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[MJW]

Hi Danny,

There's more than one way to slay this one, but the easiest I can think of
is NETWORKDAYS. The syntax is NETWORKDAYS(start_date, end_date, holidays).
So for instance, if your dates were in Column A, Rows 1 and 2, and you
weren't concerned with holidays, you can use NETWORKDAYS(a1, a2), and it'll
return the difference. If you have a range of holidays you'd like to exclude
in say, B2-B5 (like a "holidays" list of dates), you can do NETWORKDAYS(a1,
a2, B2:B5); it only discerns holidays that affect workdays, so if one falls
on a Sunday, it doesn't affect the result. Hope this helps.

Jamie W.

 

[Danny C]

Thanks Ron & MJW
I will be giving both of these a try.
This will help out a lot in determining the "days to grad" I have to use for
my students.

 

[MJW]

Not a problem, Danny. But Ron did bring up a salient point--my solution will
only work if you have the analysis pack added on. If you *don't*, you may
want to take a moment to add it, it's well worth it. It can be added via
Tools--Add-In's, and check the box for Data Analysis' related add-in's, if I
recall correctly.

Jamie W.

 

[Ron Rosenfeld]

How do you calculate days between dates BUT not count the SAT & SUN in that
range?

I know you have to change everything to days of the week, but how do you
delete (or not count) SAT & SUN's

Check out the NETWORKDAYS worksheet function in HELP.
--ron

 

[Danny C]

THANKS FOR THE TIPS

I can't use the NETWORKDAYS funtion (don't have it) but the other one
=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))
Seems to work just fine. It doesn't do Holidays, so I'll have to check out
the rest of the posts.

Danny

 

[David Biddulph]

In a default installation the Analysis ToolPak will have been installed on
your machine but not enabled, so it should be easy to enable NETWORKDAYS.
Try Tools/ AddIns, and select Analysis ToolPak.

 

[Danny C]

Thanks for the reply, however when doing so there is nothing in the Add Ins
Dialog box (completely blank). So it probably was not loaded on install
(before I got here).

Thanks again
Danny

 

[Ron Coderre]

If Holidays will be involved...try this:

With a list of holidays in cells J1:J5
C1: =SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),2)<6)*ISNA(MATCH(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),$J$1:$J$5,0)))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[daddylonglegs]

If you want to exclude holidays too you can extend Ron C's suggestion above,
i.e.

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))-SUMPRODUCT(--(holidays>=A1),--(holidays<=B1),--(WEEKDAY(holidays,2)<5))

where holidays is a named range containing your holiday dates

 

[daddylonglegs]

Sorry typo in the above, 5 at the end should be 6, i.e.

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))-SUMPRODUCT(--(holidays>=A1),--(holidays<=B1),--(WEEKDAY(holidays,2)<6))

 

[Danny C]

WOW - YOU GUYS ARE AMAZING!
HOW DO YOU COME UP WITH THIS STUFF?

THANKS A BUNCH

 

[Myrna Larson]

Hi, Ron:

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))

I'm always interested in how things work.

I realize that the "B1-A1" part of the formula is added in 5 times (because
there are 5 numbers in the array constant), then divided by 7, so it
calculates the number of workdays in the full weeks (number of full weeks *
5).

And the (WEEKDAY(A1-{2,3,4,5,6}) part determines how many days in the last
partial week fall between Monday and Friday, inclusive. I realize that the
values 2,3,4,5,6 are the weekday numbers for Monday through Friday. But can
you explain how this part of the formula works? I've been puzzling over it
ever since you posted it...

Thanks.

Myrna Larson
(ex-MVP, Excel)

 

[Ron Coderre]

Hi, Myrna! It's good to hear from you.

For ease of explanation, let's use this variation
of the formula (with Mon as DAY 1):
=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+B1-A1)/7))

and a 1-day range: 01-JAN-2007 through 01-JAN-2007

The EndDate - StartDate difference, in this case, is zero.

Now, we'll deal with the WEEKDAY section.
Since 01-JAN-2007 is a Monday, its weekday is: 1
Subtracting 1 day from 01-JAN-2007 makes it a Sunday (weekday 7).
Subtracting 2 days from 01-JAN-2007 makes it a Saturday (weekday 6).
etc.returning an array of:
{7,6,5,4,3}

When that array is added to the EndDate - StartDate difference
of zero, the result is that same array:
{7,6,5,4,3}

Dividing each array element by 7 returns:
{1, 0.857, 0.714, 0.571, 0.428}

Truncating each element with the INT function results in:
{1, 0, 0, 0, 0}

That array represents the number of times Mon, Tue, Wed, Thu, and Fri
occur in the StartDate thru EndDate period.

If our range was 02-JAN-2007 (Tue) through 02-JAN-2007
The final array would be:
{0, 1, 0, 0, 0}

If our range was 01-JAN-2007 (Mon) through 08-JAN-2007 (Mon)
The final array would be:
{2, 1, 1, 1, 1}

Summing up that array returns: 6 days

I hope that helps.
--------------------------

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

I think I can do a (hopefully) better job of explaining....

With
A1: (StartDate)
B1: (EndDate)

This formula returns the number of Mondays in that range:
=INT((WEEKDAY(A1-1,2)+B1-A1)/7)

It does that by subtracting the DayNum of the day we're counting (Mon is DAY
1) from the StartDate then calculating the DayNum.
So...
Mon (DAY1) becomes a Sunday (DAY7).
Tue (DAY2) becomes a Monday (DAY1).
etc..

The EndDate minus StartDate difference is then adjusted by that value.

When the result is divided by 7...and truncated..
The number of whole weeks is returned, which is the count of Mondays.

Example:
A1: 01-JAN-2007 (a Monday)
B1: 08-JAN-2007 (the next Monday)

C1: =INT((WEEKDAY(A1-1,2)+A2-A1)/7)

C1: =INT((WEEKDAY("01-JAN-2007"-1,2)+"08-JAN-2007"-"01-JAN-2007")/7)
C1: =INT((WEEKDAY(31-DEC-2006,2)+7)/7)
C1: =INT((7+7)/7)
C1: =INT((14)/7)
C1: =INT(2)
C1: =2 Mondays in that range.

Consequently, to count the number of Weekdays in a range...
We adjust the StartDate by the DayNum of each workday (Mon=1, Tue=2...Fri=5)
and sum the resulting array:
=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+B1-A1)/7))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Myrna Larson]

It's good to hear from you.

So you remember! It's been a while, at least 3 years since I was involved with
the ngs!

I hope that helps.

Yes, it certainly does -- Thanks!!! I see now where my thinking was going
astray: misplaced parens:

I was breaking it down into WEEKDAY(A1) - 2, WEEKDAY(A1) - 3, etc, which
doesn't "roll over" from 1 to 7, so when A1 is a Monday, my incorrect version
would return the series 0,-1,-2,-3,-4, instead of 7,6,5,4,3.

Thanks for posting the original formula and the explanation! I hope it helps
somebody other than just me.

Myrna Larson

 

[Ron Coderre]

You're very welcome, Myrna!
You've been such a great help to me that I'm glad I could return the
favor...if only in a small way.

***********
Regards,
Ron

XL2003, WinXP

 

[Myrna Larson]

BTW, did you devise this formula for counting days of the week? It's very
ingenious, IMO!

 

[Ron Coderre]

BTW, did you devise this formula for counting days of the week? It's very
ingenious, IMO!

I agree, but I'll say, "No"...because I don't remember creating it. It's in my "formula stash", but I don't know where it came from.

(I'm sure most Excel formulas are regularly re-invented by various people who have no idea if their formula has been previously
constructed by someone else. Consequently, it's difficult to say that any one person really invented a formula, especially if they
don't post it in a forum OR they post it in a forum we don't read. By way of analogy: Eratosthenes, around 200 BC, used two sticks,
the sun's shadow, and math to PROVE that the earth was round. His work was lost(misplaced) in the Dark Ages. Subsequently, during
the Renaissance, Galileo re-figured it out..over a thousand years after Eratosthenes...and now gets all the credit. I've
constructed some formulas that I'm proud of, but I'd never go so far as to claim I invented the technique. Afer all, we're not
re-writing Excel's source code. We're just rearranging Excel's existing functionality in sometimes innovative ways. But, I'll admit
it's interesting to know who first put it on a forum post, though.)

Hmmmm...was that a rant? I hope not...It wasn't meant to be.

--------------------------

Best Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)