On Wed, 4 Nov 2009 02:21:40 -0800 (PST), Abdul
wrote:

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

With your date of interest in A1:

Days since start of month
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&":"&A1)))<6))

Days to end of month
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1+1 &":"&EOMONTH(A1,0))))<6))

If you want to exclude Thursday and Friday, then, using the same technique:


Days since start of month
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&"":""&A1)))<6)*
(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&"":""&A1)))<5))

Days to end of month
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1+1 &"":""&EOMONTH(A1,0))))<6)*
(WEEKDAY(ROW(INDIRECT(A1+1 &"":""&EOMONTH(A1,0))))<5))

OR, shorter, you can use the NETWORKDAYS function by adjusting the dates to
offset by two days. NETWORKDAYS excludes Sat and Sun; and if you offset your
dates, you can shift that to Thu and Fri

Since start:
=NETWORKDAYS(A1-DAY(A1)+3,A1+2)

To end:
=NETWORKDAYS(A1+3,EOMONTH(A1,0)+2)



--ron