Abdul,

Since you don't want to count the starting date, use this to shift the start
date by one, which will take care of the Friday miscount issue as well:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&(E4+1))), 2)<5))

And to count the number of days that aren't the starting date, or a Thursday
or Friday:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&(E4+1))),2)<4))+SUMPR ODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&(E4+1))),2)5))

HTH,
Bernie
MS Excel MVP





"Abdul" wrote in message
...
Thanks,

I had used the above formula and various techniques suggested in
Dave's
page.

If I want to know the days passed, using the formula you siggested it
works fine. But when I want to use the same formula to get Days
remaining in the same month it do not.

For eg.

I f I want to know how many days remaining excluding fridays from
todays date till end of the month then

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&E4)),2)<5))

Where C1 = end of the month (=EOMONTH(E4))
and E4 = todays date

Then it dont give me the desired result

Like when E4= 1/10/2009 (1-Oct-09) and C1 of course will be 31/10/09

and I use the formula =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C1&":"&E4)),
2)<5)) -1

(Pls Note -1 at the end)
Gives me 25 which is correct (1 day passed ie. 1/10/09 and the
remaining days excluding Fridays = 25)

When I reach 08/10/09 it gives me 19 days remaining which is correct
When I reach 09/10/09 it gives me 18 which is not correct since
9/10/09 is a Friday the remaining number of Dayes supposed to be 19.

I want to use a formula to get the same result exluding Thursdays and
Fidays as well.

Any Workaround please?

Thanks



On Nov 4, 5:08 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Abdul,

=$C$1-D4 + 1 - SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E4&":"&C1)),1)=6))

I'm not sure if you want 2 or 1 when you use two dates that are
consecutive days - if you want 1,
then remove the +1, otherwise, leave it.

HTH,
Bernie
MS Excel MVP

"Abdul" wrote in message

...

Hi,

I am using the following formula to get number of days passed
excluding Fridays in a given Month from fisrt date to the given date
(today)

=(E4-D1+1)-(INT((E4-D1)/7)+IF(WEEKDAY(D1)+MOD(E4-D1,7)=6,1,0))

Where E4 is the end date (today) and D1 start date of the Month

and also Number of days remaing in the month excluding Fridays from
today till end of month.

=(C1-E4)-(INT((C1-(E4+1))/7)+IF(WEEKDAY(E4)+MOD(C1-(E4+1),7)=6,1,0))

Where E4 is the start date and C1 is the end date

Some how it is not working for all days.. some times it gives 1 day
difference.

It is the same case if I use a formula like this
=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(E4&":"& B1))),{6},0)),
1,0))-1

of course the cell address is different in above example.

Is there a better workaround?

Thanks