Formula for if/then and dates

jlajla24

New Member
Joined
Feb 11, 2005
Messages
46
So I have a worksheet that has rows with specific customers

The columns of interest are Invoice date & years of maintenance contract purchased. The date is in day/month/year format and the years of maintenance is a whole number in years.

I need a formula to be able to tell me if the contract is expired. I'm imagining an if/then statement that says if the date invoiced + number of years (365) is < today, fill another cell with expired. Then my conditional formatting will color code the row based on the word expired filled in this new cell. The other true or false result would yield "current".

Hope I've explained this ok. You guys are always amazing so thanks in advance.
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
Code:
=IF(TODAY()<=(J17+(365*K17));"Active";"Expired")

or (if you work with comma's)

Code:
=IF(TODAY()<=(J17+(365*K17)),"Active","Expired")

Where J17 = invoice date
Where K17 = number of years of maintenance
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
A slight variation, which would deal correctly with leap years, would be
Code:
=if((date(year(a1)+a2,month(a1),day(a1))< today(),"Expired","Current")

which assumes date invoiced is held in A1, and years of maintenance is in a2.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top