DATETIME_FORMAT formula not formatting for dates

Hi @Sean_Burgess - can you explain what the formula should do as I can’t work it out from looking at it?

I can see one thing that is incorrect:

DATEADD({Contract End copy})

(and DATEADD({Policy End Date}) )

DATEADD takes the form:

DATEADD([date], [#], 'units')

so I would expect this to be something like:

DATEADD({Contract End copy}, 10, 'days')

if you are trying to add to a date.

JB

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.

Thanks

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.

Hi Justin,

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.

Is there a way I can solve this?

Thank you again.

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})

IF({Policy End Date}, {Policy End Date}, {Contract End copy})

Hi Justin,

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.

Thank you again for your help - all sorted now.

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.