Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D1&":"&E4)),2)<5))
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
Thanks Dave,
I had used the above formula and various techniques suggested in your 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:43*pm, Dave Peterson wrote: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D1&":"&E4)),2)<5)) =sumproduct() likes to work with numbers. *The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail hehttp://www..xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html Abdul wrote: Hi, I am using the followingformulato 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 aformulalike 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
Instead of subtracting 1, change it to check to see if those dates are friday.
=yourformula - (weekday(e4,2)=5) - (weekday(c1,2)=5) Abdul wrote: Thanks Dave, I had used the above formula and various techniques suggested in your 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:43 pm, Dave Peterson wrote: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D1&":"&E4)),2)<5)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail hehttp://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html Abdul wrote: Hi, I am using the followingformulato 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 aformulalike 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 -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
Hi Dave,
If I use =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(E4&":"&C1)),2)<5)) - (WEEKDAY(E4,2)=5) - (WEEKDAY(C1,2)=5) then on 1/10/2009 itself i get 26, but actual result supposed to be 25 (1 day used ie 1/10/2009 and days left excluding fridays 25) and even for other days it do not work properly. Thanks for the input On Nov 5, 5:28*pm, Dave Peterson wrote: Instead of subtracting 1, change it to check to see if those dates are friday. =yourformula - (weekday(e4,2)=5) - (weekday(c1,2)=5) Abdulwrote: Thanks Dave, I had used the aboveformulaand various techniques suggested in your page. If I want to know the days passed, using theformulayou siggested it works fine. But when I want to use the sameformulato 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 theformula=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 aformulato get the same result exluding Thursdays and Fidays as well. Any Workaround please? Thanks On Nov 4, 5:43 pm, Dave Peterson wrote: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D1&":"&E4)),2)<5)) =sumproduct() likes to work with numbers. *The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail hehttp://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html Abdulwrote: Hi, I am using the followingformulato 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 aformulalike 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
please check my post on your similar thread of
microsoft.public.excel.worksheet.functions. -- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number of days within a period excluding Fridays
Thanks MRT.
I think we are there.. :-) On Nov 7, 10:26*am, "MRT" wrote: please check my post on your similar thread of microsoft.public.excel.worksheet.functions. -- MRT "Abdul" wrote in ... Hi, I am using the followingformulato 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 aformulalike 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to get number of days excluding Fridays in a given period | Excel Worksheet Functions | |||
add number of days within a department for a given time period | Excel Worksheet Functions | |||
Number of days in a running period | Excel Worksheet Functions | |||
count work days excluding Fridays and Saturdays ??? | Excel Worksheet Functions | |||
Finding number of particular days in a period | Excel Worksheet Functions |