Calculate days between completion date and today with error for blanks

[Anonymous]

Hello

I'm trying to work out the days between 2 dates and have an error for blanks. 

Currently I'm using this which calculates between the expiry date and today. 

Expired days = value(TODAY()) - value('learning obj'[Actual Expiry Date])

This works fine until the expiry date is blank. How can I add in so that if its got no expiry date it says no expiry date instead?

[Anonymous]

Work perfectly. 

Thank you 🙂 

[Mohammad_Refaei · Accepted solution]

I assume you are using a calculated column not a measure and it will be difficult to have different data types like numerical figures and text values. I would return a standard value in response to blank expiry date like 99999.

Expiry Days =
IF (
    'learning obj'[Actual Expiry Date] = BLANK (),
    99999,
    TODAY () - 'learning obj'[Actual Expiry Date]
)

if you are ok with storing the days in text format then you can use a formula like this:

Expiry Days =
IF (
    'learning obj'[Actual Expiry Date] = BLANK (),
    "No Expiry Date",
    FORMAT ( TODAY () - 'learning obj'[Actual Expiry Date], 0 )
)