I’ve got dates from two different columns being drawn into a main column that I need to be able to filter and sort.
To get the data into the main column I cobbled together different formulae from this forum, however when I try to format the column it gives me the error message “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”
The formula I’m using is:
IF( {Policy End Date} = “”, DATETIME_FORMAT(DATEADD({Contract End copy}), “LL”),DATETIME_FORMAT(DATEADD({Policy End Date}), “LL”))
The {Contract End copy} and {Policy End Date} columns are look-ups from two other tables and I also can’t format this data because of the same error message around not being a number or date.
Does anyone have a suggestion around changing the formula so that I can format the column for dates?
Hi John, thank you for getting back to me so quickly.
As a company we have clients on either of two different types of contracts - therefore we’ve got a table for each of these with their contract details. What we’ve then got is another ‘master’ table that brings in the data from these two tables so we’ve got a single table to view and compare all clients.
One of the key columns we need to view is date of expiry for the contracts. I don’t think it is possible to bring in data from two different tables into a single column, so instead I’ve got two columns in the master table that look-up the expiry dates from each of the other two other tables.
The formula essentially says that if column {Policy End Date} is blank then take the data from the {Contract End copy}, otherwise take the data from column {Policy End Date}. As clients can’t have both types of contracts, they will only ever have data in one column.
The extra “LL” is to view the column as ‘friendly’ date format, only because it wouldn’t allow me to format the column as a date field.
I hope my goal makes sense, even if me formula doesn’t.
Welcome to the community, @Sean_Burgess! :grinning_face_with_big_eyes:
Thanks for clarifying. In this case, DATEADD() isn’t necessary. As @JonathanBowen pointed out, it’s used when you want to take an existing date and move forward or backward from that date by a certain amount.
Because you’re just choosing which date to format, I recommend putting DATETIME_FORMAT() as the outermost layer of this formula, using IF() on the inside to pick which date is to be formatted. This makes the resulting formula much more compact:
DATETIME_FORMAT(IF({Policy End Date}, {Policy End Date}, {Contract End copy}), “LL”)
Also notice that I didn’t need to compare {Policy End Date} to an empty string. By using only the field reference in the first part of the IF() function, I’m simply looking at the field contents. A field with data in it will always evaluate as TRUE, with an empty field evaluating as FALSE. So this shortcut version basically says, “If {Policy End Date} has data, use it. Otherwise, use {Contract End copy}.” The result of that comparison is then formatted.
Thank you for your help with this and making the formula much cleaner.
With your formula i’m getting the same end result which is great, but I still can’t format the column as a date. The main reason I want to format as a date is so I can filter it. For instance I want to filter to clients contracts expiring in January 2020. At the moment if I try to filter this column it’s treating it as text fields.
That’s because the date has been formatted as text by DATETIME_FORMAT(). If you want to leave it as a date (actually a datetime object internally) for date-based comparisons, remove the formatting layer of the formula and leave only the inner IF() function:
IF({Policy End Date}, {Policy End Date}, {Contract End copy})
Thank you for your help with this. That was the original formula I started with but it wasn’t allowing me to format as a date.
I have now traced back the other columns that it’s linked too and realised that a formula in one of the tables had DATEADD in the IF statement. By removing this it then turned that column and all others into dates - which I could then format in the master table.
FWIW putting a DATEADD() function inside an IF() function shouldn’t be a problem in itself. Most likely one of the two functions wasn’t formatted correctly, causing the overall formula to no longer output a date.
