Help needed with an IF statement (2002)

Topic

New Reply

Cell B7 is a date. In cell B8 I am trying to write an IF statement that will perform different calculations depending on the day of the week the date in B7 falls on. If the date in B7 is a Saturday, add 3 (days) to B7. If the date in B7 is a Sunday, add 2 (days) to B7. My problem is I don’t know how to write the IF statement in such a way that it knows what day the date is in B7. Any advise would be much appreciated. Many thanks.

Reply | Quote

Replies

What should B8 contain if B7 is not a Saturday or Sunday? If you want the same date as in B7:

=B7+IF(WEEKDAY(B7)=7,3,IF(WEEKDAY(B7)=1,2,0))

And if you want the next day:

=B7+IF(WEEKDAY(B7)=7,3,IF(WEEKDAY(B7)=1,2,1))

WEEKDAY(B7) returns a number that indicates the day of the week, where 1 = Sunday, 2 = Monday, …, 7 = Saturday.

Reply | Quote

Many thanks. Boy would I struggle without the fantastic support from Woody’s Lounge!

Reply | Quote

Following on from the formula you suggested above, how could this be amended to take into account the following:

1. If cell B6=”Saturday” (as text) and the date in B7 is a Saturday then don’t add anything. Otherwise perform the rest of the formula you have stated.

2. The formula also needs to take into account bank holidays. So if B7 = Saturday, add 3 days but if the Monday is a Bank Holiday, add 1 more day. If B7 = Sunday, add 2 days but if the Monday is a Bank Holiday, add 1 more day. If B7 isn’t a Saturday or Sunday, don’t add anything.

How would I structure my IF statement to cover these scenarios? I know I need to enter a range of dates that would be my Bank Holidays.

Really appreciate your advice again. Thanks.

Reply | Quote

1. Something like

=B7+IF(WEEKDAY(B7)=7,IF(B6=”Saturday”,0,3),IF(WEEKDAY(B7)=1,2,0))

2. I’ll look at that later on.

Reply | Quote

How about modifying Hans’ formula for 2 (presuming that D1:D10 has a list of dates that are bank holidays):

=B7+IF(WEEKDAY(B7)=7,IF(B6=”Saturday”,0,3),IF(WEEKDAY(B7)=1,2,0))+ISNUMBER(MATCH(B7+1,$D$1:$D$10,0))+ISNUMBER(MATCH(B7+2,$D$1:$D$10,0))

Note if B7 is the Bank Holiday, it is not a Saturday nor Sunday so would not change. Is this what you are after?

Steve

Reply | Quote

Thank you Hans and Steve. I would never have figured that out! I’m still carrying out some testing but first impressions look very promising. I will post back to confirm one way or the other. Thanks again.

Reply | Quote

The latest formula you’ve suggested does do what I expect SOME of the time. Attached is the spreadsheet which will make it easier to explain. The idea is for a user to enter an order date and a working day leadtime (if there is one). Excel calculates this into calendar days. The user then has to select a delivery service. I’ve then used cell B7 to calculate a temporary date so that further calculations can be carried out in cell B8. My idea was that row 7 would be hidden so the user only sees the final result in B8. I’m happy to scrap row 7 if one formula in B8 can achieve all of this. A delivery date must only ever occur on a Saturday if the user has selected a Saturday delivery in B6. Deliveries CANNOT ever take place on a Sunday or Bank Holiday. So if 2 working days delivery is selected this must count 2 working days AND take into account bank holidays (range Z18:Z44). Making the order date 22/08/2008 and then choosing your delivery service is a good way to see how the formula responds (as the 25th was a UK bank holiday). I accept that the spreadsheet has evolved a little since my posts yesterday! Thanks, Mark

Reply | Quote

Does the attached version do what you want? It uses a custom function WorkdayPlus that includes Saturdays as working days:

Function WorkdayPlus(Start_Date As Date, Days As Long, Optional Holidays) As Date
Dim d As Date
Dim n As Long
Dim varItm As Variant
If Days = 0 Then
WorkdayPlus = Start_Date
Else
d = Start_Date
Do
d = d + 1
If Weekday(d) > 1 Then
n = n + 1
If Not IsMissing(Holidays) Then
For Each varItm In Holidays
If d = varItm Then
n = n – 1
Exit For
End If
Next varItm
End If
End If
Loop Until n = Days
WorkdayPlus = d
End If
End Function

Reply | Quote

Hans, I’m getting ‘#NAME?’ in cell B6.

Reply | Quote

What happens if you edit the cell then press Enter without changing anything? (The error is caused by Excel’s flawed handling of Analysis Toolpak functions in international versions)

Reply | Quote

No change.

Reply | Quote

How about this version? It doesn’t use the Analysis ToolPak at all.

Reply | Quote

Hans,

This works perfectly. Unfortunately I need the Analysis ToolPak running to use the formula =WORKDAY(B3,B4,Z16:Z42)-B3 in cell B5 (Calendar Days Lead Time) on another worksheet. Unless you can suggest another way of calculating this? See attached. Thanks, Mark

Reply | Quote

There is no need to uninstall Analysis ToolPak; you can keep on using it.

I just worked around it because translation issues make it impossible for me to solve your problem using Analysis ToolPak. I’m using the Dutch language version of Excel.
Excel will automatically translate standard worksheet functions such as SUM or MATCH to the local language of the user, so you’ll see English function names and I’ll see Dutch function names.
However, this translation does not occur for Analysis ToolPak functions. So the WORKDAY function causes an error when I open your workbook. I can repair that by changing WORKDAY to its Dutch equivalent, but then you will get an error when you open the workbook, as you have found.

Reply | Quote

Ok. Understood.

Reply | Quote

Here is a version with an extended version of the WorkdayPlus function, making the formula just a tiny bit shorter.
I also used it to calculate the Calendar Days Lead Time.

Reply | Quote

Thank you. In addition to the formula Hans provided above that worked off a working day value, I also need a formula (in B17) to work off a calendar day value (B15) as per the attached spreadsheet. I suspect Hans’ formula just needs a slight modification but I can’t figure this out. For example todays date is Tues 9 Sept. Calendar days lead time is 5 (but this falls on a Sunday). Delivery service chosen is ‘Standard -2 working days’. So the delivery date should equal Wed 17 Sept. (This is because the supplier won’t hand over the goods to the courier until the Monday 15th). Thanks again.

Reply | Quote

I’m afraid I’ve lost the plot. Can you explain in detail EXACTLY how the delivery date should be calculated in different circumstances?

Reply | Quote

How about in B8: (I don’t use any of the intermediate formulas)
=CHOOSE(MATCH(B6,$Y$18:$Y$20,0), WORKDAY($B$3,2,$Z$18:$Z$44), WORKDAY($B$3,1,$Z$18:$Z$44), IF(WEEKDAY(B1+1)=7,B1+1, WORKDAY($B$3,1,$Z$18:$Z$44)))

Though personally I would prefer inserting the 3 formulas next to each of lookup values in Y and then use a Vlookup.

Steve

Reply | Quote

Steve,

You don’t take the leading workdays value into account.

Reply | Quote

I kept thinking I was making it too simple. Your UDF is probably better for a variety of reasons…

Steve

Reply | Quote

One way to do this would be to use =CHOOSE(WEEDAY(B7),3,1,monVal,tueVal,wedVal,thuVal,frVal)

Reply | Quote