Weekday/workday

[2Busy]

Okay. Say you promise to have a product to a customer on or before a certain date stored in cell L2. You have the number of transit days it takes the product to get to the customer in cell K2. We can figure that we have to ship the product by date SUM(L2,-K2). This returns weekends and holiday dates in certain instances, so we switch to the WORKDAY function. I get:

Please Login or Register  to view this content.

This is half-way there. Shipping companies pull freight through weekends, and the 2nd (-K2) argument isn't counted on weekends.

I need to have a WORKDAY returned. This is the day that the product will be loaded. I need the 2nd argument to be counted in weekends.

I get to something like:

Please Login or Register  to view this content.

Just to test I make the formula:

Please Login or Register  to view this content.

where L2=9/14/2010 and K2=3. The result is 1/1 but SUM(L2,-K2) returns a Saturday.

I should be seeing the above forumla yield 1/0. I think I have more problems than anticipated here.

Can anyone help me get to this end-point:

Please Login or Register  to view this content.

?
I do not how to evaluate the bolded part, but I need the IF statement to put the calculation on the 'value if true' argument before I have to worry about that.

I'm open to any suggestions!

Thanks!
-2Busy

[daddylonglegs]

Perhaps try like this

=WORKDAY(L2-K2+1,-1,Holidays[Date Off])

[2Busy]

Thank You for your quick response!

What is the +1 for on the end of your first argument? I assume the -1 for the second argument is to get the count back to the first workday before the start date? I didn't think WORKDAY could work here because the number of days to count back could be 1 to 3 days if a holiday is on a Monday. I had tried -0.

Or you +1 the first argument and -1 the second argument to negate the +1 simply because the second argument is required?

[daddylonglegs]

By adding 1 and then going back one workday you get the original date (given by L2-K2) whenever that date is a working day - for example if L2-K2 gives you a Wednesday then L2-K2+1 will give the Thursday....and WORKDAY will give the previous workday...back to Wednesday

....but if L2-K2 is a Sunday or Saturday the previous workday to either the Monday or Sunday is the Friday, so it'll give you the previous workday, is that what you need?

[2Busy]

Yes it works perfectly I just wanted to make sute I understood the logic there. As I said I had tried -0 for the second argument just to try and get the function to count backwards to the nearest workday from the start date and it didn't work. I see now how -1 can move you 2 or 3 days since weekends and holidays aren't counted. I've tested this on several dates that wrap around weekends and/or holidays. Works great AND makes sense!

Thanks again!