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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
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))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=557543

=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"
wrote in message
news:daddylonglegs.2a9mup_1151755803.028@excelforu m-nospam.com...

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.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=557543

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"
wrote in message
news:daddylonglegs.2ae77c_1151968802.4846@excelfor um-nospam.com...

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

Hany ElKady Wrote:
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)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=557543