Work Days Formula

Anyone know how I can calculate the working days between two dates on a
sheet. My working week is from Sunday - Thursday, and the dates are stored in
pairs with start date and end date.

I am leaveing VB as the last resort for this.

Any Ideas ?

--
Hany ElKady
Professional Services Architect
Technology & Service Delivery

Hi!

Try this:

A1 = start date
B1 = end date

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))

This doesn't account for holidays. If you need to account for holidays post
back.

Biff

"Hany ElKady" <[email protected]> wrote in message
news:[email protected]...
> Anyone know how I can calculate the working days between two dates on a
> sheet. My working week is from Sunday - Thursday, and the dates are stored
> in
> pairs with start date and end date.
>
> I am leaveing VB as the last resort for this.
>
> Any Ideas ?
>
> --
> Hany ElKady
> Professional Services Architect
> Technology & Service Delivery

Thanks Biff, that is a very nice trick with the row formula, never knew you
could do that, can you explain also the meaning of the "- -" part ?

Is there a way to change the standard weekdays since you are assuming
saturday sunday are off.

--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"Biff" wrote:

> Hi!
>
> Try this:
>
> A1 = start date
> B1 = end date
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))
>
> This doesn't account for holidays. If you need to account for holidays post
> back.
>
> Biff
>
> "Hany ElKady" <[email protected]> wrote in message
> news:[email protected]...
> > Anyone know how I can calculate the working days between two dates on a
> > sheet. My working week is from Sunday - Thursday, and the dates are stored
> > in
> > pairs with start date and end date.
> >
> > I am leaveing VB as the last resort for this.
> >
> > Any Ideas ?
> >
> > --
> > Hany ElKady
> > Professional Services Architect
> > Technology & Service Delivery
>
>
>

Hi!

>can you explain also the meaning of the "- -" part ?

This expression will return an array of boolean TRUEs and FALSEs:

WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6

The "--" converts those to 1's and 0's (TRUE = 1, FALSE = 0)

And then Sumproduct just sums those numbers.

For more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

> Is there a way to change the standard weekdays since you are assuming
> saturday sunday are off.

The formula as written is based on Friday Saturday off.

The Weekday function uses 3 possible arguments to define when a week starts
and ends:

1 (used by default if omitted) = Sunday = 1 - Saturday = 7
2 = Monday = 1 - Sunday = 7
3 = Monday = 0 - Sumday = 6

Since I omitted that argument the default is 1: Sunday = 1 - Saturday = 7

Under the default, Thursday is weekday 5.

So, the formula is simply counting the weekdays that are less than weekday 6
(Friday).

Biff

"Hany ElKady" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff, that is a very nice trick with the row formula, never knew
> you
> could do that, can you explain also the meaning of the "- -" part ?
>
> Is there a way to change the standard weekdays since you are assuming
> saturday sunday are off.
>
> --
> Hany ElKady
> Professional Services Architect
> Technology & Service Delivery
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> A1 = start date
>> B1 = end date
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6))
>>
>> This doesn't account for holidays. If you need to account for holidays
>> post
>> back.
>>
>> Biff
>>
>> "Hany ElKady" <[email protected]> wrote in message
>> news:[email protected]...
>> > Anyone know how I can calculate the working days between two dates on a
>> > sheet. My working week is from Sunday - Thursday, and the dates are
>> > stored
>> > in
>> > pairs with start date and end date.
>> >
>> > I am leaveing VB as the last resort for this.
>> >
>> > Any Ideas ?
>> >
>> > --
>> > Hany ElKady
>> > Professional Services Architect
>> > Technology & Service Delivery
>>
>>
>>

On Fri, 30 Jun 2006 22:43:01 -0700, Hany ElKady
<[email protected]> wrote:

>Anyone know how I can calculate the working days between two dates on a
>sheet. My working week is from Sunday - Thursday, and the dates are stored in
>pairs with start date and end date.
>
>I am leaveing VB as the last resort for this.
>
>Any Ideas ?


=NETWORKDAYS(StartDate+1,EndDate+1)

If you need to include holidays, then try this **array-entered** formula:

=NETWORKDAYS(StartDate+1,EndDate+1,Holidays+1)

To **array-enter** a formula, after typing it in, hold down <ctrl><shift> while
hitting <enter>. Excel will place braces {...} around the formula.


--ron

My preference would be to use Ron's NETWORKDAYS suggestion but if you don't want to use Analysis ToolPak functions and you don't need to exclude holidays....

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

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

What's the logic behind this?

I fully understand the "mechanics" but what's the logic?

Biff

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> My preference would be to use Ron's NETWORKDAYS suggestion but if you
> don't want to use Analysis ToolPak functions and you don't need to
> exclude holidays....
>
> =SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=557543
>

Hi Biff,

This formula

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

gives a total of all Suns, Mons, Tues Weds and Thus in the period A1 to B1. It's an extension of a formula like

=INT((WEEKDAY(A1-1)+B1-A1)/7)

which will give a count the number of Sundays between A1 and B1 (inclusive).

I find it easiest to explain with an example. If A1 is 1st June and B1 is 30th of June, how many Sundays are there between the two?

The answer can be deduced if the weekday of 1st June is known, if it's a Saturday or a Sunday then there are 5 Sundays in the period, otherwise 4.

Equating that to the above formula, when A1 is a Sunday

=INT((WEEKDAY(A1-1)+B1-A1)/7) becomes

=INT((7+29)/7)=5

when A1 is a Saturday..

=INT((6+29)/7)=5

but when A1 is a Thursday...

=INT((5+29)/7)=4

etc.

The formula can be used to count any day or combination of days between any two dates.

>The formula can be used to count any day or combination of days between
>any two dates.

Yes, I've tested it rather extensively.

Although the solution I offered isn't the most efficient, having to generate
an array and using a volatile function, but I do think it's more intuitive,
at least it is to me. I couldn't figure the logic behind the other formula.

Biff

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Biff,
>
> This formula
>
> =SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))
>
> gives a total of all Suns, Mons, Tues Weds and Thus in the period A1 to
> B1. It's an extension of a formula like
>
> =INT((WEEKDAY(A1-1)+B1-A1)/7)
>
> which will give a count the number of Sundays between A1 and B1
> (inclusive).
>
> I find it easiest to explain with an example. If A1 is 1st June and B1
> is 30th of June, how many Sundays are there between the two?
>
> The answer can be deduced if the weekday of 1st June is known, if it's
> a Saturday or a Sunday then there are 5 Sundays in the period,
> otherwise 4.
>
> Equating that to the above formula, when A1 is a Sunday
>
> =INT((WEEKDAY(A1-1)+B1-A1)/7) becomes
>
> =INT((7+29)/7)=5
>
> when A1 is a Saturday..
>
> =INT((6+29)/7)=5
>
> but when A1 is a Thursday...
>
> =INT((5+29)/7)=4
>
> etc.
>
> The formula can be used to count any day or combination of days between
> any two dates.
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=557543
>

That is a very clever way of doing it. Just for completness sake, and so that
everyone may benifit. Do you have another trick to remove certain days
(holidays) from the list while counting ?

--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"daddylonglegs" wrote:

>
> My preference would be to use Ron's NETWORKDAYS suggestion but if you
> don't want to use Analysis ToolPak functions and you don't need to
> exclude holidays....
>
> =SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=557543
>
>

Originally Posted by Hany ElKady

Just for completness sake, and so that
everyone may benifit. Do you have another trick to remove certain days
(holidays) from the list while counting ?

As I said above, I think Ron's Networkdays suggestion is the easiest but......

You could exclude holidays with

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

where holidays is a named range containing all holiday dates

although it might be simpler just to extend Biff's suggestion to

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<6),--ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),holidays,0)))