Formula - Date if a condition is true

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am trying to use the formula - Date field to calculate a date that is nine months in the future from a specific date.  This part is easy...  I've used AdjustMonth([Date Due], 9).  But I only want a date to populate if it is actually due.  We have some projects that are exempt and would rather see an "NA" in this field if its not needed.  Can If statements be used with date formulas?

Should I use a different format?  

Thanks in advanced.
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
A formula date field can only calculate to result in a date, and not text like "N/A".

But you can make a formula text field like this

IF([Exempt]= true, "N/A", ToText(AdjustMonth([Date Due], 9)))

If you are not happy with the way the date appears in text format, post back with an example of the format you want and I can help you.
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
Works Great.  Thank you.  I'm trying to flowing the same logic for another field, but thinking I have too many variables...

I need a field to return one of two due dates if a  specific field is not picked...

The three fields are:
Short or Long? - Multiple Choice
Due date- date field
Extension due date- Date field only if a box is checked.

If Short is chosen, then return "NA".  If long is chosen, then I need it to return a date 30 days after either the due date or extension due date if box is checked.  

Any Thoughts?
If you can say the formula in words then Quick Base can do the formula.

Try this

IF(
[Short or long]="Short", "NA",
[Box is checked], [Extension Due Date] + Days(30),
[Due date] + Days(30))
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
I keep getting Type- Mismatch error...  saying the Field is a text type and days(30) is duration.  Probably because one of the due dates I'm using is the original date we calculated in the first place using a text formula?
Yep,

A date field type is a date field type and a text field type is a text field type even if it calculates to some words that happen to look like a crazy formatted date with background text coloring.

So, you can't add 30 days to random "blah blah blah" words.  "blah blah blah" plus 30 days is not something that makes sense.
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
Is there a way to format the original equation as a date field and instead of putting NA, it is just blank?
sorry, My answer above was wrong.  

IF(
[Short or long]="Short", null,
[Box is checked], [Extension Due Date] + Days(30),
[Due date] + Days(30))