Formual Based Date Not Allowing Date Based Filtering

[CodeKnight]

As you can see a formula that generates a date.

Also as you can see, no filtering options by date for this formula field.

Have tried a RAW and unformatted date in the formula which achieves the same result.

[Ben_Young1]

Hey @CodeKnight,

Is the formula outputting a date/time data type?
The filter indicates that your formula field returns a string instead of date/time data.

Is your formula referencing a string for its data?
If so, you’ll need to parse it into the correct data type.

I’m in the middle of working on a project requiring working with a formula that parses date/time data from a string, so this is fresh on my mind.

For reference:

[kuovonne]

CodeKnight:

As you can see a formula that generates a date.

In your screen shot, it looks like you have a formula that produces a text string that looks like a date to human eyes, but is not date to the computer.

CodeKnight:

Have tried a RAW and unformatted date in the formula which achieves the same result.

Can you share screen shots of the formula configuration and of any input fields that the formula uses?

[CodeKnight]

“Reoccurs Every (Days)” is a number

“Latest Work Report” is a Date

[CodeKnight]

The Date Time Format seems to be the issue. Which is a real shame, as it means i need a seperate field just to make it user friendly to read, and hard to read field for the filters…

[ScottWorld · Accepted solution]

@CodeKnight Airtable does not make these things very easy, so you are correct that you will have to jump through a few hoops here to make this work.

You will ultimately end up with 2 formula fields, one for viewing and one for filtering.

1. You are correct that DATETIME_FORMAT results in a text string, but even if the 2nd part of your formula resulted in an actual date, you would still need to rework your formula field so that it ONLY results in a date and NEVER results in a text string. (See #3 below.) Currently, your formula can sometimes result in an empty string of text, which will throw off Airtable because it can sometimes result in text… even if the latter half of your formula returned an actual date. (See #3 below.) So just keep your existing formula field for onscreen cosmetic viewing purposes only.

2. Since DATETIME_FORMAT always returns a string of text, you will have no choice but to create yet another date formula field that actually results in an actual date under all circumstances, and use that additional formula field for your filtering.

3. In your new date formula field, you will need to rework the formula so that it only outputs a date under all circumstances by removing the empty string output. Just get rid of the first part of your IF statement (the part where you say if it is equal to blank text, result in blank text). Simply say IF that field exists, then immediately go into the date portion of your formula. But remember that you should not use DATETIME_FORMAT either. So you will need to do 2 things: you will need to change the logic of your formula, and you will need to remove the DATETIME_FORMAT function as well.

So now you will have 2 formula fields — one for viewing and one for filtering.

Sorry to be the bearer of bad news here, but look on the bright side: Airtable is the best way that I know of to give your head a workout against a brick wall! :rofl: