|
Formula to get number of days excluding Fridays in a given period
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 |
Formula to get number of days excluding Fridays in a given period
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 |
Formula to get number of days excluding Fridays in a given period
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 |
Formula to get number of days excluding Fridays in a given period
passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7)
remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 |
Formula to get number of days excluding Fridays in a given period
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 |
Formula to get number of days excluding Fridays in a given period
passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7)
remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
How I can Modify this formula to get days passed and remaing excluding both Thursdays and Fridays? Thanks, On Nov 6, 5:15*pm, MRT wrote: passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
passed Weds: .....D1-3.....
passed Thus: .....D1-4..... passed Fris: .....D1-5..... remaing Weds: .....E4-3..... remaing Thus: .....E4-4..... remaing Fris: .....E4-5..... HTH -- MRT "Abdul" wrote in message ... How I can Modify this formula to get days passed and remaing excluding both Thursdays and Fridays? Thanks, On Nov 6, 5:15 pm, MRT wrote: passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
Thanks,
There is no cut short to use more than one day at a time? Like both Thursday and Friday or any other combination (In a single formulae)? Abdul On Nov 8, 1:05*pm, "MRT" wrote: passed Weds: .....D1-3..... passed Thus: .....D1-4..... passed Fris: .....D1-5..... remaing Weds: .....E4-3..... remaing Thus: .....E4-4..... remaing Fris: .....E4-5..... HTH -- MRT "Abdul" wrote in ... How I can Modify this formula to get days passed and remaing excluding both Thursdays and Fridays? Thanks, On Nov 6, 5:15 pm, MRT wrote: passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
e.g.
passed Thus & Fris: =INT((E4-D1+WEEKDAY(D1-4,3)+1)/7) + INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) -- =SUM(INT((C13-B13+WEEKDAY(B13-{4,5},3)+1)/7)) HTH -- MRT "Abdul" wrote in message ... Thanks, There is no cut short to use more than one day at a time? Like both Thursday and Friday or any other combination (In a single formulae)? Abdul On Nov 8, 1:05 pm, "MRT" wrote: passed Weds: .....D1-3..... passed Thus: .....D1-4..... passed Fris: .....D1-5..... remaing Weds: .....E4-3..... remaing Thus: .....E4-4..... remaing Fris: .....E4-5..... HTH -- MRT "Abdul" wrote in ... How I can Modify this formula to get days passed and remaing excluding both Thursdays and Fridays? Thanks, On Nov 6, 5:15 pm, MRT wrote: passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
sorry,
e.g. passed Thus & Fris: =INT((E4-D1+WEEKDAY(D1-4,3)+1)/7) + INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) -- =SUM(INT((E4-D1+WEEKDAY(D1-{4,5},3)+1)/7)) "MRT" wrote in message ... e.g. passed Thus & Fris: =INT((E4-D1+WEEKDAY(D1-4,3)+1)/7) + INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) -- =SUM(INT((C13-B13+WEEKDAY(B13-{4,5},3)+1)/7)) HTH -- MRT "Abdul" wrote in message ... Thanks, There is no cut short to use more than one day at a time? Like both Thursday and Friday or any other combination (In a single formulae)? Abdul On Nov 8, 1:05 pm, "MRT" wrote: passed Weds: .....D1-3..... passed Thus: .....D1-4..... passed Fris: .....D1-5..... remaing Weds: .....E4-3..... remaing Thus: .....E4-4..... remaing Fris: .....E4-5..... HTH -- MRT "Abdul" wrote in ... How I can Modify this formula to get days passed and remaing excluding both Thursdays and Fridays? Thanks, On Nov 6, 5:15 pm, MRT wrote: passed: =E4-D1+1-INT((E4-D1+WEEKDAY(D1-5,3)+1)/7) remains: =C1-E4-INT((C1-E4+WEEKDAY(E4-5+1,3))/7) HTH -- MRT "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 . |
Formula to get number of days excluding Fridays in a given period
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 |
Formula to get number of days excluding Fridays in a given period
it seems that unexpected result returns at every end of month,
because if A1 is EOM, A1+1 gives 1st of next month. -- MRT "Ron Rosenfeld" wrote in message ... Days to end of month =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1+1 &":"&EOMONTH(A1,0))))<6)) To end: =NETWORKDAYS(A1+3,EOMONTH(A1,0)+2) |
Formula to get number of days excluding Fridays in a given period
On Mon, 9 Nov 2009 19:49:01 +0800, "MRT" wrote:
it seems that unexpected result returns at every end of month, because if A1 is EOM, A1+1 gives 1st of next month. Since you didn't elaborate, I am assuming the unexpected result comes from the formula that counts the days to the end of the month, and that the expected result would be a zero. If you mean something else, please be more specific. Using the technique, you can make a special case for the end of the month: =IF(A1=EOMONTH(A1,0),0,SUMPRODUCT(--(WEEKDAY( ROW(INDIRECT(A1+1 &":"&EOMONTH(A1,0))))<6))) and for the formula to exclude Thursdays and Fridays: =MAX(0,NETWORKDAYS(A1+3,EOMONTH(A1,0)+2)) or =IF(A1=EOMONTH(A1,0),0,SUMPRODUCT((WEEKDAY(ROW(IND IRECT( A1+1&":"&EOMONTH(A1,0))))<6)*(WEEKDAY(ROW(INDIREC T( A1+1&":"&EOMONTH(A1,0))))<5))) --ron |
Formula to get number of days excluding Fridays in a given period
Your assumption absolutely right, sorry for my unspecific expression.
-- MRT "Ron Rosenfeld" wrote in message ... On Mon, 9 Nov 2009 19:49:01 +0800, "MRT" wrote: it seems that unexpected result returns at every end of month, because if A1 is EOM, A1+1 gives 1st of next month. Since you didn't elaborate, I am assuming the unexpected result comes from the formula that counts the days to the end of the month, and that the expected result would be a zero. If you mean something else, please be more specific. Using the technique, you can make a special case for the end of the month: =IF(A1=EOMONTH(A1,0),0,SUMPRODUCT(--(WEEKDAY( ROW(INDIRECT(A1+1 &":"&EOMONTH(A1,0))))<6))) and for the formula to exclude Thursdays and Fridays: =MAX(0,NETWORKDAYS(A1+3,EOMONTH(A1,0)+2)) or =IF(A1=EOMONTH(A1,0),0,SUMPRODUCT((WEEKDAY(ROW(IND IRECT( A1+1&":"&EOMONTH(A1,0))))<6)*(WEEKDAY(ROW(INDIREC T( A1+1&":"&EOMONTH(A1,0))))<5))) --ron |
Formula to get number of days excluding Fridays in a given period
On Mon, 9 Nov 2009 21:40:00 +0800, "MRT" wrote:
Your assumption absolutely right, sorry for my unspecific expression. -- MRT Did my modifications suit your requirements? --ron |
Formula to get number of days excluding Fridays in a given period
"Ron Rosenfeld" wrote in message ...
Did my modifications suit your requirements? I think it is suit, however, since my answer (the following) is also based on assuming, I'm not so sure whether suit for Abdul's requirements. passed exc Fris & Thus: =E4-D1+1-SUM(INT((E4-D1+WEEKDAY(D1-{4,5},3)+1)/7)) remaining exc Fris & Thus: =C1-E4-SUM(INT((C1-E4+WEEKDAY(E4-{4,5}+1,3))/7)) Anyway I loved your simple formula using NETWORKDAYS, and learnt a lot. Thanks. -- MRT |
Formula to get number of days excluding Fridays in a given period
Hello,
Passed: =E4-D1+1-SUM(INT((E4-MOD(E4-{5,6},7)-D1+7)/7)) Remaining: =C1-E4-SUM(INT((C1-MOD(C1-{5,6},7)-E4+6)/7)) Source: http://sulprobil.com/html/date_formulas.html Actually, it seems to be the same as MRT's - just WEEKDAYS replaced by MOD... Regards, Bernd |
| All times are GMT +1. The time now is 05:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com