Need to countdown to a due date, results in years, then months, then days to 0

I am tracking when something is approaching a due date. I want to subtract the due date from the current date (already referenced in another cell, if that helps) and have the results show as time remaining in a cell as follows:

If > 1 year, show time remaining as "x.x Years"
If < 1 year, show time remaining as "x.x Months"
If <=90 days, show time remaining as "x Days"
If <0, show the words "Overdue"

So depending on the time remaining, I would like the result to show in the cell like these examples: 2.2 Years, or 10.5 Months, or 72 Days.

Can anyone come up with a good way to do that?

A1 cel shows Todays date and c1 cell shows due date
=ROUND(IF((C1-A1)>365;(C1-A1)/365;IF(AND((C1-A1)<365;(C1-A1)>90);(C1-A1)/12;C1-A1));1)&IF((C1-A1)>365;" years";IF(AND((C1-A1)<365;(C1-A1)>90);" months";" days"))

Sorry, forgot to add "overdue"
=IFERROR(ROUND(IF((C1-A1)>365;(C1-A1)/365;IF(AND((C1-A1)<365;(C1-A1)>90);(C1-A1)/12;IF(AND((C1-A1)<90;(C1-A1)>0);C1-A1;"")));1);"")&IF((C1-A1)>365;" years";IF(AND((C1-A1)<365;(C1-A1)>90);" months";IF(AND((C1-A1)<90;(C1-A1)>0);" days";" overdue")))

Thank you for the response. I apologize for the delay getting back to you, I wanted to verify the formula at work. I tried the formula at home, but it didn't work. I only had Excel 2003, and 2007 at home, so I wanted to try it on 2010 at work before I wrote back.

I inserted the "today" function in A1, and put an arbitrary future date in C1, then pasted your formula in another cell. In each of the Excel programs I tried it on, it said there was an error, and it wouldn't run. Excel pops up a message saying the formula contains an error, then it highlights the first "365", but no other information.

I haven't tried to analyze the formula yet, but I'll take a closer look today. In the meantime if you have any helpful tips I would appreciate it.

semicolons in formula need to be replaced with commas.



=IFERROR(ROUND(IF((C1-A1)>365,(C1-A1)/365,IF(AND((C1-A1)<365,(C1-A1)>90),(C1-A1)/12,IF(AND((C1-A1)<90,(C1-A1)>0),C1-A1,""))),1),"")&IF((C1-A1)>365," years",IF(AND((C1-A1)<365,(C1-A1)>90)," months",IF(AND((C1-A1)<90,(C1-A1)>0),"days","overdue")))

Thanks for the tip on the commas, that got it to work!

Well, I mean the formula will run now, but the Month calculation is off. Over 90 days, and less than a year, it does return Months, but it is an exaggerated amount of months, not the actual amount of months. Any thoughts on that?

Also I changed the years and days < symbols to <=, otherwise it would return "Overdue" when the difference was exactly one year, or 90 days.

I'm sure eisayev will post back with correction , but in the mean time try the formula below and see if it works out.



=IFERROR(ROUND(IF((C1-A1)>365,(C1-A1)/365,IF(AND((C1-A1)<365,(C1-A1)>90),(C1-A1)/30,IF(AND((C1-A1)<=90,(C1-A1)>0),C1-A1,""))),1),"")&IF((C1-A1)>365," years",IF(AND((C1-A1)<365,(C1-A1)>90)," months",IF(AND((C1-A1)<=90,(C1-A1)>0),"days","overdue")))

That one seems to do the trick!

It's funny how you can get blinded from what should be obvious. I looked at the (C1-A1)/12 and said, yeah, OK, 12 months in a year, but seeing the divide by 30 instead of 12 it all made sense! It gets a little funky right before the year mark, showing 12.1 months instead of 12, but for all intents and purposes this will be great.

Thank you so much, tommyt61 and eisayev!

For posterity, in case anyone refers to this later on, here is the final (as of today, anyway!) version of the formula I am using:

=IFERROR(ROUND(IF((M10-$C$1)>383,(M10-$C$1)/365,IF(AND((M10-$C$1)<=383,(M10-$C$1)>365),(M10-$C$1)/365,IF(AND((M10-$C$1)<=365,(M10-$C$1)>90),(M10-$C$1)/30.4,IF(AND((M10-$C$1)<=90,(M10-$C$1)>0),M10-$C$1,"")))),1),"")&IF((M10-$C$1)>383," Years",IF(AND((M10-$C$1)<=383,(M10-$C$1)>365)," Year",IF(AND((M10-$C$1)<=365,(M10-$C$1)>90)," Months",IF(AND((M10-$C$1)<=90,(M10-$C$1)>0)," Days","Overdue"))))

A couple of the screwy things I did:

1. Changed the month calculation to 365/30.4 (instead of 365/30), to make the months slightly more accurate, and not end up with over twelve months to go when the time is still under a year left.

2. Made it so when the time remaining is over 1 year, but less that 1.1 years, it reads "Year", not "Years". (I didn't like it saying "1 Years"!)

3. Made it so the formula can be copied to other cells by making the "Today" date (C1 on my spreadsheet) a constant ($C$1). M1 in my formula above is where I have the due date for that particular item, but now I can copy and paste that formula to other items and the due date cell number will change, but not the "Today" date.

One question I have: Is there any way to round off the "Days" number (when M10-$C$1 on my formula above is <=90) to a whole number? I like having the years and months show a decimal place, but I don't need to see that something is due in 80.3 days.

By the way, this formula does exactly what I wanted it to do, so once again I wanted to say thank you to tommyt61 and eisayev!