# Thread: IF Calculation (2003 SP2)

1. ## 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. ## 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>

3. ## Re: IF Calculation (2003 SP2)

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

Steve

4. ## Re: IF Calculation (2003 SP2)

This worked!

Thanks, Diane

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

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

7. ## Re: IF Calculation (2003 SP2)

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

Thanks! Diane

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

Try this:

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

11. ## 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. ## 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. ## 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
•