Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    IF Calculation (2003 SP2)

    If A1 (e.g. 9/31/07) falls after the 20th of its month, then A2 should be the 20th of the following month (e.g. 10/20/07.

    If it falls on or before the 20th of the month (e.g. 9/19/07), then it should be the 20th of that month (e.g. 9/20/07).

    Can you help me do this? I don't understand how to respond to the IF command options.

    Thanks! Diane

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    Try the formula below in A2:

    <code>
    =IF(DAY(A1)>20,DATE(YEAR(A1),MONTH(A1)+1,20),DATE( YEAR(A1),MONTH(A1),20))
    </code>
    Legare Coleman

  3. #3
    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: IF Calculation (2003 SP2)

    You can do it without an IF:
    =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20)

    Steve

  4. #4
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    This worked!

    Thanks, Diane

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    If I changed the date in A1 to one after 9/20 (e.g., 9/21), it did not change the month to 10.

    Thanks, Diane

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

    Re: IF Calculation (2003 SP2)

    Try again, Steve's formula does work...

  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    I will do that ... I may have revised the cells references incorrectly.

    Thanks! Diane

  8. #8
    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: IF Calculation (2003 SP2)

    You might also check that when you entered "9/20" that XL did not interpret it as 9/1/1920 .... which would be converted to 9/20/1920 and display (in mdd or myy as "9/20"

    Steve

  9. #9
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    Now they want to extend the dates an additional month. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    If the date in A1 falls after the 20th (e.g. 1/21/08) , the date in A2 should be the 20th, two months after the current month (e.g. 3/20/08).

    If the date in A1 falls on or before the 20th (e.g. 1/3/08) , the date in A2 should be the 20th, one month after the current month (e.g. 2/20/08).

    Can you please revise either or both formulas you gave me with these changes?

    Diane

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    Try this:

    <code>
    =DATE(YEAR(A1),MONTH(A1)+2*((DAY(A1)>20)),20)
    </code>
    Legare Coleman

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

    Re: IF Calculation (2003 SP2)

    Legare's formula won't return the 20th of next month if the date in A1 is on or before the 20th. Try this formula instead:
    <code>
    =DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>20),20)
    </code>
    This formula adds 1 to the current month, and 1 extra if the date in A1 is after the 20th.

  12. #12
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Calculation (2003 SP2)

    This works perfectly!

    Same form -- another calculation. I need to calculate the number of months between January (of the current year) and a specific date later in the year, and then add two months. I'm not sure how to indicate "January" of the "current year."

    Diane

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

    Re: IF Calculation (2003 SP2)

    Say that you have a date in A1.
    The month number of the date is MONTH(A1).
    The month number of January is 1.
    So the difference is MONTH(A1)-1.
    Add 2 and you get MONTH(A1)-1+2.
    The complete formula becomes

    =MONTH(A1)+1

Posting Permissions

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