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.

 

[njimack]

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....

Barry, good point.

I suppose for completeness, I should have used:
=IF((YEAR(A1)=YEAR(B1))*(MONTH(A1)=MONTH(B1)),"OK","")

 

[Likeexcel]

All works good to me. WOW, I'm just learning new things everyday from this board Again Many thanks for all the help.

 

[Likeexcel]

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.

Hi Jon: What about this?
currently i have this formula
IF(R29="","",WORKDAY(N29,VLOOKUP(E29, Y:AG,9,FALSE),$U$5:$U$118))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Column N is ship date.<o></o>
<o> </o>
Column E is customer #<o></o>
Column U is a list of days I want to not include (i.e list of weekend dates and US holidays).<o></o>
<o> </o>
I want to add a column to identify which customer is international and which one is domestic ( I have that list).<o></o>
<o> </o>
Now with the formula above, could I also add that if it’s international customers exclude these list of holidays days and if it’s US customers then exclude a different list of holidays?<o></o>
<o> </o>
Thank you in advance.
sorry for all the questions.<o></o>

 

[barry houdini]

Firstly, you don't need to include weekend dates in the column U list - WORKDAY excludes weekends anyway - you only need to list MF dates that need to be excluded

Assuming you list US holidays in V5:V118 (with existing list in U5:U118 for International) then with INT or US in column S try this version

=IF(R29="","",WORKDAY(N29,VLOOKUP(E29, Y:AG,9,FALSE),IF(S29="US",$V$5:$V$118,$U$5:$U$118)))

 

[Likeexcel]

Firstly, you don't need to include weekend dates in the column U list - WORKDAY excludes weekends anyway - you only need to list MF dates that need to be excluded

Assuming you list US holidays in V5:V118 (with existing list in U5:U118 for International) then with INT or US in column S try this version

=IF(R29="","",WORKDAY(N29,VLOOKUP(E29, Y:AG,9,FALSE),IF(S29="US",$V$5:$V$118,$U$5:$U$118)))

so if S29="US", THEN COLUMN V for domestic days
IF S=29="INT" THEN COLUMN U for international days

thank you again.