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

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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!

Photo of Amber

Amber

  • 632 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
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]))
Photo of Amber

Amber

  • 632 Points 500 badge 2x thumb
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.
Photo of Amber

Amber

  • 632 Points 500 badge 2x thumb
Hmm, no, that didn't help.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
Sorry:  I gave it a shot.
Photo of Amber

Amber

  • 632 Points 500 badge 2x thumb
The strange thing is that the absolute value of the number seems to be correct, whether it's positive or negative.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
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]))