How this formula be improved to check for text N/A

[dd8]

I have inherited a spreadsheet from someone. The original formula works out the number of days.
DAYS TILL DUE = Due Date - todays date

DAYS TIL DUE
=[@[DUE DATE]]-TODAY()

The column is formatted as Number. Due Date column contains a mix of date and text 'N/A' If no date the user enters N/A

Cells containing N/A the formula result is an error displaying #VALUE!

How can the formula be edited to clean up to ..
Check for text 'N/A' and the result 'No date'
Else
work out Days Till Due

TIA

 

[SanjayGMusafir]

The column is formatted as Number. Due Date column contains a mix of date and text 'N/A' If no date the user enters N/A

Cells containing N/A the formula result is an error displaying #VALUE!

Check this and revert -

=Ifs(ISNA([@[DUE DATE]]),"No Date",True,[@[DUE DATE]]-TODAY())

 

[dd8]

@


Example of values in
DUE DATE
2/11/2024
2/11/2024
N/A
N/A
29/12/2024
30/08/2024
N/A
N/A


I add
=Ifs(ISNA([@[DUE DATE]]),"No Date",True,[@[DUE DATE]]-TODAY())

The returned results are incorrect

18/07/1900

18/07/1900

#VALUE!

#VALUE!

 

[SanjayGMusafir]

There was mistake in understanding. Change the formula to

=Ifs([@[DUE DATE]]="N/A","No Date",True,[@[DUE DATE]]-TODAY())

 

[myall_blues]

=IFERROR([@[DUE DATE]]-TODAY(),"No date")

Regards

Murray

 

[dd8]

Many thanks. I used the following to work. all good ..

=IFERROR([@[DUE DATE]]-TODAY(),"No date")

 

[SanjayGMusafir]

Many thanks. I used the following to work. all good ..

=IFERROR([@[DUE DATE]]-TODAY(),"No date")

Would suggest marking it as solution. It helps other to quickly navigate when visiting thread.

 

[dd8]

done, and thanks again ..

 

[Fluff]

In future please mark the post that contains the solution, I have changed it for you.