add dates

[Likeexcel]

Hello:
Not sure if there is a formula that in any given calendar dates it know would add weekend days and holidays.

For instance,if we ship something on 9/28 wednesday and it takes 4 business days to get to the customer, so it gets to the customer on tuesday 10/4.
However if i added 4 days to 2011-09-28, it gives me 2011-10-02(Sunday) because it's including weekend days. i need a formula that says on any given date plus the 4 business days to ship then give me the date, but if that date falls on a weekend or holiday dates then add the extra weekend dates or holidays, so the return date is on a working date.

not sure if this is possible, may need to have extra table for the calendar dates?

thank you in advance.

 

[Jonmo1]

Try

=WORKDAY(A1,4,E1:E20)

Where A1 is your start date
4 is number of working days to add
E1:E20 is an OPTIONAL range containing holidays to be excluded as well.

This requires the Analysis toolpack from Tools - Addins


Hope that helps.

 

[njimack]

Like this? I don't have a list of US holidays, so I've only entered 2 - you'll need to populate the rest.

Excel Workbook
	A	B	C	D	E	F	G
1	Ship date	Delivery date
2	28-Sep	04-Oct				New Yrs Day	01-Jan
3						Xmas Day	25-Dec
Sheet1

 

[Likeexcel]

Thank you both.

Just one more question, is there an easy way to popular all the weekend dates for the year?

 

[barry houdini]

Try putting the first weekend date of the year in A2 and then this formula in A3 copied down

=IF(WEEKDAY(A2)=1,6,1)+A2

 

[Likeexcel]

Thank you.

also just one more if cell A1 has the 2011-08-28 as the format (or any dates for that matter), and B1 has let say 2011-09-01 as the format just as an example. what is the formula shows if A1 and B1 are not the same month then "", otherwise "ok"?

thank you again.

 

[barry houdini]

One way....

=IF(A1-DAY(A1)=B1-DAY(B1),"OK","")

 

[Likeexcel]

Thank you Barry. you guys are awesome!

 

[njimack]

Another method...
=IF(MONTH(A1)=MONTH(B1),"OK","")

 

[barry houdini]

Hello Neil ....I suppose it depends on your definition of "same month" - assuming that it must be the exact same calendar month, i.e. the same month in the same year - then my method would do that, comparing just the month, of course, won't ensure that the months are in the same year....