How set values without expiration date?

Hello to everyone,
I have a little problem with the data type. In particular, I have a list of certificate of training course but some course have a expiration date and other no. The problem is for the certificate without expiration date because in app view appears “invalid date” where in DB is set the values “NO”. How can I show in the app that for some certificate there is no expiration date instead of “invalid date”
Schermata 2021-05-20 alle 01.09.49

TKS in advance!

Are you actually using a database? Or are you using a spreadsheet?

Either way, a column of a temporal type (date, time, or date/time) should not ever have NO as a value.

1 Like

Hi Steve,
I’m using a spreadsheet in which the columns “EXPIRATION DATE” contains or the date or the value NO. How can i have a better visual in the app for the training courses that don’t have an expiration date?

Why not just leave the cell blank if there is no expiration date?

1 Like

Because my idea is also to track in a chart view the expiration date in order to monitoring the date and how many certificate are expiring.
How you can see, the first histogram is blank… i would not that the values blank appears in my chart

1 Like

@DaGi07 Could you achieve your goal using a virtual column instead of the real one?

To start with, I would definitely not mess with the database (see @Steve ). “No” is a no-no as a date. That said, a blank value would be perfectly fine to convey the fact that the expiration date is not set.

If the need is to filter out the invalid dates, then a slide would indeed do the job. But again, just leave these dates blank.

Otherwise, if you actually want to show all dates, including the blank one, but display No instead of [blank] like it is in your chart, you could try using a virtual column for instance.

Let’s call it [VirtualDate], and make it of type Text (not Date). It would display the actual date if set, or ‘NO’ if blank.

The formula could look like:

IF ( ISBLANK( [Date] ), “No”, [Date] )

Not sure it would sort properly in the chart, but it definitely would show “No” instead of “[blank]”. Just an idea, trying to work around your issue.


Firstly, I would like to thanks you for your the great attention that you give to my question.
Unfortunately, the values in the graph are not sorted as you told me. Maybe, I tried to filter the invalide date and remove these date from the graph.
The result is this that you can see in the screen.