Making if-then formula to consider day of the week

**McBane** · 03-02-2011, 11:40 AM

I have the formula of what I want to do worked out in my head, I just don't know how to get it to come out on excel.

When I add the transit date to a packing date it just adds the number of days (including weekends) to the date to come out iwth the delivery date, and thus, I'm forced to look at a calendar to find the next weekday for us if it happens to land on a weekend.

So it comes out like this (packing date) + (days in transit) = (delivery date) without any regard for the day of the week.

What I need is to create a cell that basically does this:

If (Delivery date) = Sunday, then + 1
If (delivery date) = Saturday, then + 2

Please help me

**NBVC** · 03-02-2011, 11:51 AM

If you install the Analysis Toolpak through Tools|Addin

Then you will have functions like Networkdays() and Worday().. which are designed to automatically exclude weekends (and any holidays you define in a table). Have a look at these functions in Excel Help.

**McBane** · 03-02-2011, 12:03 PM

Originally Posted by NBVC

If you install the Analysis Toolpak through Tools|Addin

Then you will have functions like Networkdays() and Worday().. which are designed to automatically exclude weekends (and any holidays you define in a table). Have a look at these functions in Excel Help.

I've tried these and the dates that come up are workdays.

I want total days to be added in the calculation and THEN defer to the following workday. for example, if the transit time is 5 days and I snet the package on march 1, 2011 (tuesday) it should come out as march 6, 2011 (sunday) and then I want it to add a day so that it comes out as march 7, 2011 (monday).

When using the workday() it comes out as March 8, 2011.

**daddylonglegs** · 03-02-2011, 12:06 PM

If you want to add 100 (calendar) days to a date in A2 and then get the next workday (if the result isn't one already) use this formula

=WORKDAY(A2+100-1,1)

**McBane** · 03-02-2011, 12:23 PM

Originally Posted by daddylonglegs

If you want to add 100 (calendar) days to a date in A2 and then get the next workday (if the result isn't one already) use this formula

=WORKDAY(A2+100-1,1)

Thanks alot, how do I do the opposite?

**daddylonglegs** · 03-02-2011, 12:30 PM

What do you mean, get the start date given end date and number of days?

**McBane** · 03-02-2011, 12:32 PM

Determine the start date based on the end date and the number of days, yes. Cuz I tried doing

=WORKDAY(100-A2+1,-1)

and it doesn't seem to work.

**daddylonglegs** · 03-02-2011, 12:42 PM

You can't say with certainty. For example if you have a Monday as the start date and 5 days to add then that will fall on the Saturday so the delivery date is the following Monday, you also get the same delivery date for Tuesday with 5 days transit and Wednesday with 5 days transit......so it follows that when you work that in reverse, if Monday is the delivery date and transit time is 5 days that the packing date could be any of 3 dates.

If delivery date is C2 then this formula

=WORKDAY(C2-100+1,-1)

....will give the latest possible sent date

**McBane** · 03-02-2011, 12:57 PM

Pack day(this is B4): 01/12/11
number of days(this is C4): 25
Delivery date (this is D4): 02/07/11

The formula should be

=WORKDAY(B4+C4-1,1) in order for 02/07/11 to come out.

now I tried this in reverse, like you showed:

If D4 is the delivery date then:

=WORKDAY(D4-C4+1,-1) should come out to be 01/12/11

Instead it comes out as 01/13/11.

**daddylonglegs** · 03-02-2011, 01:09 PM

Yes, that's what I said.

For any given delivery date and number of days there could be more than one possible packing day. 01/13/11 is valid because if you add 25 days to that date you'll get 02/07/11 too (and you'd also get that delivery date adding 25 days to 01/11/11)....so there are 3 possible dates that could have given you that delivery date with 25 days transit.....so it isn't possible to know which date it is, the formula will give you the latest date if there is more than one possibility