Formula to return both a calculation and text?

[mlight]

I'd like to build a field that returns the days until due if >0, or "Assign Due Date" if Nan, or "Passed Due" if <0.

I am struggling to combine the IF statement with the following formula:

DATETIME_DIFF(

{Deadline},

TODAY(),

'days'

)
Any tips on how to accomplish this?

[mlight]

Solved! Took a lot of trial and error, but got there in the end.

IF(

DATETIME_DIFF(Deadline,TODAY(),'days')>0,

DATETIME_DIFF(Deadline,TODAY(),'days'),

IF(DATETIME_DIFF(Deadline,TODAY(),'days')<0,

"Passed Due",

"Assign Due Date"

)

)

[Alexey_Gusev]

Hi,
Glad that you solved it, but maybe this wll help you in future how to 'check for non-emptiness'. When Deadline is empty, you want to display 'Assign Due Date', and if assigned, show the state (Number of days to do or message when it is no more days)
So, it should be done in a following way:

IF({Deadline},  something if Deadline set , something if Deadline empty )

In your case it's
 

IF(Deadline, 
  IF(DATETIME_DIFF(Deadline,TODAY(),'days')>0,
  DATETIME_DIFF(Deadline,TODAY(),'days'),
  'Passed Due'),
'Assign Due Date')

[kuovonne]

Also keep in mind that even though the number of days looks like a number, from Airtable’s point of view, it is text. Airtable will not be able to directly perform math on those values. If you only want human eyes to see the values, and don’t need to perform additional calculations, that might be fine. 

[disdirect]

To build a field that returns the days until due if greater than 0, "Assign Due Date" if the date is not assigned, or "Passed Due" if less than 0, you can combine the IF statement with the DATETIME_DIFF formula in Airtable. Here’s how you can structure the formula:

IF(
    {Deadline},
    IF(
        DATETIME_DIFF({Deadline}, TODAY(), 'days') > 0,
        DATETIME_DIFF({Deadline}, TODAY(), 'days') & " days",
        IF(
            DATETIME_DIFF({Deadline}, TODAY(), 'days') < 0,
            "Passed Due",
            "Due Today"
        )
    ),
    "Assign Due Date"
)

This formula first checks if the {Deadline} field is not empty. If it is empty, it returns "Assign Due Date." If the deadline is set, it calculates the difference in days between the deadline and today. If the difference is greater than 0, it returns the number of days until the deadline. If the difference is less than 0, it returns "Passed Due." If the difference is exactly 0, it indicates "Due Today." This approach ensures that all conditions are covered effectively. 

Njmcdirect