Work Days Formula

H

Hany ElKady

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 ?
 
B

Biff

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
 
H

Hany ElKady

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 said:
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
Click to expand...
 
B

Biff

Hi!
can you explain also the meaning of the "- -" part ?
Click to expand...

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.
Click to expand...

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 said:
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.
Click to expand...
 
R

Ron Rosenfeld

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 ?
Click to expand...


=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
 
D

daddylonglegs

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))
 
D

daddylonglegs

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.
 
B

Biff

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

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
 
H

Hany ElKady

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 ?
 
D

daddylonglegs

Hany said:
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 ?
Click to expand...

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)))
 
Top