# 'Months until Expired' or 'Expired on (Date)' field

• 0
• Question
• Updated 3 years ago

I have a field called [date expires] and I want to create a field to display 'expired on' if the date is past, and 'x months' if the date is not yet reached.

My plan involved creating a lot of extra fields.

• [days until expired] , which will convert to
• [months until expired], which will plug into
• [expiration description], which would display 'Months until Expired' if date is not past, or 'Expired on (Date)' field

But I'm having trouble figuring out how to convert days number to months, and I'm not sure that this is the most elegant method.

Thank you!

• 632 Points

Posted 3 years ago

• 0

dwhawe, Champion

• 908 Points
This is untested but I believe you can do it like this:

If(

Today()<[Date Expires],"Months until expired "&"ToText(Month([Date Expires])-Month(Today()))",

"Expired on "ToText([Date Expires]))
• 632 Points
Hmm. Seem close!

I removed the quotes around the month conversion formula.

It's funny, I'm getting negative month numbers for some months and not for others, which makes me think the Month(Today) should be on the other side of the minus sign and visa versa.
• 632 Points
Hmm, no, that didn't help.

dwhawe, Champion

• 908 Points
Sorry:  I gave it a shot.
• 632 Points
The strange thing is that the absolute value of the number seems to be correct, whether it's positive or negative.

dwhawe, Champion

• 908 Points
QuickBase has an absolute value function if you have not already found it.  Also, I did not account for years so it could get messy with periods that cross years.

You may need that months until expired field as a formula duration set to months with this:  [Date Expires]-Today()

Then the formula would be:

If(

Today()<[Date Expires],"Months until expired "&"ToText([Months Until Expired)",

"Expired on "ToText([Date Expires]))