Results 1 to 13 of 13
Thread: IF Calculation (2003 SP2)

20070913, 20:25 #1
 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

20070913, 20:40 #2
 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

20070913, 20:50 #3
 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

20070913, 23:33 #4
 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

20070913, 23:35 #5
 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

20070914, 00:12 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: IF Calculation (2003 SP2)
Try again, Steve's formula does work...

20070914, 15:34 #7
 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

20070915, 00:05 #8
 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

20080103, 02:27 #9
 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

20080103, 03:52 #10
 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

20080103, 11:13 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080103, 19:34 #12
 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

20080103, 19:37 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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