Discussions

Expand all | Collapse all

Formula - Date if a condition is true

  • 1.  Formula - Date if a condition is true

    Posted 11-24-2017 18:25
    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.


  • 2.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 19:28
    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.


  • 3.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 20:20
    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?


  • 4.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 20:28
    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))


  • 5.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 20:39
    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?


  • 6.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 20:48
    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.


  • 7.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 21:10
    Is there a way to format the original equation as a date field and instead of putting NA, it is just blank?


  • 8.  RE: Formula - Date if a condition is true

    Posted 11-24-2017 21:31
    sorry, My answer above was wrong.  

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