Complex Date Functions
 

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

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


--

Regards,

Peo Sjoblom

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

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

-----Original Message-----
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
.

Ooops!

Should be:

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

Biff

-----Original Message-----
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

-----Original Message-----
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
.

.

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

Complex Date Functions
 

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

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom

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

Complex Date Functions
 

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.


--
Biff
Microsoft Excel MVP


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

"Peo Sjoblom" wrote:

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


--

Regards,

Peo Sjoblom

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