Unable to skip sunday...while adding 4 days in a date in XL2007

[sam143sta]

Hi,

I require urgent help in solving my problem regarding my project in Excel 2007 using win-XP.

I hope you experts can help me getting instant solution to my problem.
______________________________________________________________



For Ex:

Dispatch Date is 01/01/2011 (Saturday) & Delivery date which should be 4 days later from the dispatch date.

Here Sunday is not a working day. It has to be skipped during the count.

please remember i only want to skip Sunday....

So please find me a solution/formula to solve this problem.

Thanks in advance!

 

[Yahya]

welcome to MrExcel Board

Excel Workbook
	C
3	01/01/2011
4	06/01/2011
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C4	=+C3+4+IF(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&C3+4)))=1)),1,0)

 

[Just Jan]

Cell A1 is dispatch date

Cell B1 =IF(WEEKDAY(A1)=1,"Date is a Sunday",IF(WEEKDAY(A1)+4<8,A1+4,A1+5))

 

[sam143sta]

Thanks Yahya,

It works but i have one more if the dispatch date itself is sunday... then i need a output N.A. both in dispatch date and delivery date... how I get this output ?

For Ex:

Dispatch Date Day of Dispatch Date Delivery date
03/07/2010 Sunday 3/12/2010

Need Output :
Dispatch Date Day of Dispatch Date Delivery date
N.A. Sunday N.A.

Can you please help me out for the same...

Thanks & Regards,
Sam

 

[Yahya]

try this

Excel Workbook
	A	B	C
1	Dispatch Date	Day of Dispatch	Date Delivery date
2	03/07/2011	Sunday	#N/A
3	04/08/2011	Thursday	09/08/2011
Sheet5

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(WEEKDAY(A2)=1,NA(),A2+4+IF(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&A2+4)))=1)),1,0))

 

[barry houdini]

For a more generic formula......this allows you to add any number of days (in B1) to a date (in A1) while skipping only Sunday

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))