Complex Date Functions

[Guest]

Hi, I have two dates (E8 = 17/02/05 & E9 =26/02/05).
I want to know how many days in that range are Fridays, Saturdays or Sundays
as I am building a work schedule to calcuate days not worked and men only
work Mon to Thursday.
I need to use standard excel functions as the workbook must be compatiable
on all computer (even without toolpak installed).
Any idea would be appreciated.

Thanks

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E8&":"&E9)),2)>=5))

totals 5 which is correct

to get the non fri-sun days use

=1+E9-E8-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E8&":"&E9)),2)>=5))

format as general or else you probably get a weird date

 

[Biff]

Hi!

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=SUM(N(WEEKDAY(ROW(INDIRECT(E8&":"&E9)))={1,5,6}))

Biff

 

[Biff]

Ooops!

Should be:

=SUM(N(WEEKDAY(ROW(INDIRECT(E8&":"&E9)))={1,6,7}))

Biff

 

[Guest]

Many thanks to both of you. Both methods worked perfectly and I can only say
thank you for such a prompt and accurate reply.

 

[Victor]

i have a problem with this, cause I Dont Know what to do, in my case i have
the following formula, cell E8 = TODAY() & E9 = TODAY() + E10

 

[T. Valko]

i have a problem with this, cause I Dont Know

what to do, in my case i have the following formula,
cell E8 = TODAY() & E9 = TODAY() + E10

What's the problem?

The formula should work if you have those formulas in E8 and E9.