Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding my country's weekends
I have a start date in cell A1 ( 10/05/2007 ) ,specific number in cell b1 ( 5
) then i need to caculate the due date which equals A1+A2 excluding the weekends noting that my coutry's weekends are ( friday & saturday ). i ll be so grateful if any body helps me. Thanks Karam |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding my country's weekends
hey i just did this the other day
i did it in 2 steps first step: create a list of dates excluding weekends going down a column cell A1 = today() cell A2 = IF(WEEKDAY(a1+1)=1,a1+2,IF(WEEKDAY(a1+1)=7,a1+3,a1 +1)) cells A3:500 = extend the formula downwards a long way second step: build the bit which works it out cell B1 = (your date) cell B2 = (how many dates to add) cell B3 = index(A1:A500,match(B1,A1:A500,0)+B2,1) I suppose you could get this to work in one step if you can be bothered to think about it but I can't. -- Allllen "Karam" wrote: I have a start date in cell A1 ( 10/05/2007 ) ,specific number in cell b1 ( 5 ) then i need to caculate the due date which equals A1+A2 excluding the weekends noting that my coutry's weekends are ( friday & saturday ). i ll be so grateful if any body helps me. Thanks Karam |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding my country's weekends
This should pretty much do it...
http://www.excelforum.com/showthread.php?t=349613 Write back if you need more help. Regards, Ryan--- -- RyGuy "Karam" wrote: I have a start date in cell A1 ( 10/05/2007 ) ,specific number in cell b1 ( 5 ) then i need to caculate the due date which equals A1+A2 excluding the weekends noting that my coutry's weekends are ( friday & saturday ). i ll be so grateful if any body helps me. Thanks Karam |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding my country's weekends
Sorry I just realised I was doing it for Sat and Sun.
A2 = IF(WEEKDAY(a1+1)=6,a1+3,IF(WEEKDAY(a1+1)=7,a1+2,a1 +1)) -- Allllen "Allllen" wrote: hey i just did this the other day i did it in 2 steps first step: create a list of dates excluding weekends going down a column cell A1 = today() cell A2 = IF(WEEKDAY(a1+1)=1,a1+2,IF(WEEKDAY(a1+1)=7,a1+3,a1 +1)) cells A3:500 = extend the formula downwards a long way second step: build the bit which works it out cell B1 = (your date) cell B2 = (how many dates to add) cell B3 = index(A1:A500,match(B1,A1:A500,0)+B2,1) I suppose you could get this to work in one step if you can be bothered to think about it but I can't. -- Allllen "Karam" wrote: I have a start date in cell A1 ( 10/05/2007 ) ,specific number in cell b1 ( 5 ) then i need to caculate the due date which equals A1+A2 excluding the weekends noting that my coutry's weekends are ( friday & saturday ). i ll be so grateful if any body helps me. Thanks Karam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding my country's weekends
Hummm, I just tried this function...I think that +8 should be +7
=SUMPRODUCT(INT((E1-WEEKDAY(E1+1-{2;3;4;5;6})-C1+7)/7))-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(C3:C5),{2;3;4;5; 6},0))*(C3:C5=C1)*(C3:C5<=E1)) Regards, Ryan--- -- RyGuy "ryguy7272" wrote: This should pretty much do it... http://www.excelforum.com/showthread.php?t=349613 Write back if you need more help. Regards, Ryan--- -- RyGuy "Karam" wrote: I have a start date in cell A1 ( 10/05/2007 ) ,specific number in cell b1 ( 5 ) then i need to caculate the due date which equals A1+A2 excluding the weekends noting that my coutry's weekends are ( friday & saturday ). i ll be so grateful if any body helps me. Thanks Karam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table grouping excluding weekends | Excel Discussion (Misc queries) | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Workday With Weekends Excluding Holidays | Excel Worksheet Functions | |||
Formula - Excluding weekends & holidays | Excel Worksheet Functions |