Forum Discussion

MarkComish's avatar
MarkComish
Qrew Assistant Captain
5 years ago

Formula for duration

So bad at formulas.  How would I change this formula to say if "Completed" or "Cancelled" null otherwise...
I have it working for status = completed

If([Status] = "Completed", null, ToDays(Today() - ([Due Date])) <= 0, null, Today() - ([Due Date]))

------------------------------
Mark Comish
------------------------------
Formulas and functions

2 Replies

Sort By
  • JustinLapier's avatar
    JustinLapier
    Qrew Trainee
    5 years ago

    Updated your formula:

    If([Status] = "Completed" or [Status] = "Cancelled", null, ToDays(Today() - ([Due Date])) <= 0, null, Today() - ([Due Date]))

    Alternatively, you could write it with a case statement:
    Case([Status],
    "Completed", null,
    "Cancelled", null,
    If(Today() - [Due Date] <= Days(0), null, Today() - [Due Date]))

    Edit: Freddie beat me by 1 min, minor tweak to case statement.

    ------------------------------
    Justin Lapier
    President (Lead QB Developer)
    Synctivate
    ------------------------------
  • FreddieSabbs's avatar
    FreddieSabbs
    Qrew Trainee
    5 years ago
    Hey Mark,
    If the formula is working correctly otherwise, you should just be able to append it like so:

    If([Status] = "Completed" or [Status] = "Cancelled", null, ToDays(Today() - ([Due Date])) <= 0, null, Today() - ([Due Date]))

    ------------------------------
    Freddie Sabbs
    Senior Solutions Consultant
    Quick Base
    fsabbs@quickbase.com
    Cambridge MA
    ------------------------------