Subtracting a Date from a Date?

[exceln3wb]

Hi,

=SUM(J6-A2)

Column J = A list of different Expiry Dates from 2016 (Expired) to 2020 (Expiring in 3 years).

A2 = Today's Date formula "=TODAY()".

So with this list of numbers showing the number of days until each contract expires, I'd like to do an =IF statement that breaks these down into groups ie. Expired, Expiring in <1 Month, Expiring in <3 Months, Expiring in +1 year. How can I do this?

[exceln3wb]

Instead of the "Expiring In" column showing numbers, I'd love to somehow show groupings ie. "Expired", "Expiring in <1 Month" etc.

Screen Shot 2017-10-06 at 16.51.44.png

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

Then the logical next step is to create a Pivot Table so that you can quickly view different layouts.

[exceln3wb]

That is fantastic Richard, thank you very much!

Would I be able to add 1 more thing into this formula:
In some cases, the Expiry Date cells state "Autorenew" instead of a date, which means I can ignore it. In these cases, could you make the formula cell also state "Autorenew" or is this too may arguments?

So the groups would be:
Expired
< 1 Month
< 3 Months
+ 1 Year
Autorenew

If this isn't possible then not to worry, and thanks again for your help sir!

[Richard Buttrey]

Perhaps

Please Login or Register  to view this content.

[exceln3wb]

Amazing! - thank you so much !!! enjoy your weekend !!!