Skip to main content
Question

Formula - Date if a condition is true

  • November 24, 2017
  • 0 replies
  • 221 views
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.
      This topic has been closed for replies.

      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.
      • AngelaBowman
      • Author
      • Quickbase Alumni
      • November 25, 2017
      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))
      • AngelaBowman
      • Author
      • Quickbase Alumni
      • November 25, 2017
      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.
      • AngelaBowman
      • Author
      • Quickbase Alumni
      • November 25, 2017
      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))
      Terms & ConditionsAccessibility statement