Help with if statement

Microsoft Excel 2003 (full product)
April 9, 2010 at 11:49:20
Specs: Windows XP
Its ongoing learning path with excel, I am trting to figure out if a customer would be eligible for an early upgrade based on the date that their contract is up. Lets say the customer contract is up on 11/30/2010 but they can upgrade one year early, is there a universal formula I could use for multiple columns?

See More: Help with if statement

Report •

April 9, 2010 at 12:25:59

You haven't given us a lot to work with.

If you want a formula that returns Yes or No based on whether a date is more or less than a year from now, then try this:

	A		B
	Contract	Less then
1	Termination	one year
	Date		to go
2	04/Nov/11	No
3	30/Nov/11	No
4	24/Jan/11	Yes
5	08/May/12	No
6	26/May/11	No
7	05/May/12	No
8	08/Jul/10	Yes
9	29/Mar/11	Yes
10	24/Apr/12	No

In Cell B2 enter this formula:
Drag this formula down alongside the contract termination dates in column A.

The formula is 'universal' in that you can copy it and paste it into another column to the immediate right of another column of contract termination dates.

If you want more, please provide more details of what data you already have and what cells the data is in.

I am presuming that the contract termination dates are in a standard date format recognized by Excel as a date.

In the above example if you enter =A2+1 into an empty cell it will show 05/Nov/11. This shows that the date in A2 has been recognized as a date. Adding 1 to it returns the next day.


Report •

April 9, 2010 at 12:38:13
How about this:

=IF(A1<TODAY()-365,"Upgrade Eligible","Not Eligible")

You can test this by changing the date on your computer and then forcing Excel to recalculate.

You could also change the color of the cell via Conditional Formatting by using the Formula Is option:


Please note that the TODAY() function is volatile, so if you use the IF function option, Excel is going to ask you if you want to save the file even if you do nothing to worksheet except open and close it.

Report •

Related Solutions

Ask Question