# Thread: Help needed with an IF statement (2002)

1. ## Help needed with an IF statement (2002)

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.

2. ## Re: Help needed with an IF statement (2002)

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.

3. ## Re: Help needed with an IF statement (2002)

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

4. ## Re: Help needed with an IF statement (2002)

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.

5. ## Re: Help needed with an IF statement (2002)

1. Something like

=B7+IF(WEEKDAY(B7)=7,IF(B6="Saturday",0,3),IF(WEEK DAY(B7)=1,2,0))

2. I'll look at that later on.

6. ## Re: Help needed with an IF statement (2002)

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

=B7+IF(WEEKDAY(B7)=7,IF(B6="Saturday",0,3),IF(WEEK DAY(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

7. ## Re: Help needed with an IF statement (2002)

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. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

8. ## Re: Help needed with an IF statement (2002)

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

9. ## Re: Help needed with an IF statement (2002)

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

10. ## Re: Help needed with an IF statement (2002)

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

11. ## Re: Help needed with an IF statement (2002)

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

12. ## Re: Help needed with an IF statement (2002)

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)

13. ## Re: Help needed with an IF statement (2002)

No change. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

14. ## Re: Help needed with an IF statement (2002)

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

15. ## Re: Help needed with an IF statement (2002)

Steve,

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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•