Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help needed with an IF statement (2002)

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

  4. #4
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.

    Really appreciate your advice again. Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #8
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #11
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help needed with an IF statement (2002)

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #13
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help needed with an IF statement (2002)

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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with an IF statement (2002)

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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed with an IF statement (2002)

    Steve,

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

Page 1 of 2 12 LastLast

Posting Permissions

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