Re: Concatenate formula fields with formatted date/time

Jessie_Jane · ‎May 05, 2020

Hello!

I’m doing my nut in trying to collect info from 2 (formula) columns into one, with the right (date) formatting.

Here’s the sitch:
I have subscribers who can register for 1 month, or 3 month blocks. I want to show their renewal date in one column.

I’ve started with creating a “Month Start” field (the date their month-long registration started) and a “3 Month Start” field (the date their 3-month registration started).

Next I’ve made 2 DATEADD columns:
“Month End” adds 1 month to the “Month Start” date.
“3 Month End” adds 3 months to the “3 Month Start” date.

So far so good.

Then, I’ve created a new column called “Renew Date”. I want it to pull the dates from “Month End” and “3 Month End” (where they exist; some cells in each column are blank).

This works, to an extent, using CONCATENATE…

{Month End}&{3 Month End}

This pulls the info in, but it’s using that bastard format “2020-07-08T00:00:00.000Z”.

So I’ve tried to throw in the DATETIME_FORMAT:

DATETIME_FORMAT({Month End},‘DD MMMM YYYY’)
& DATETIME_FORMAT({3 Month End},‘DD MMMM YYYY’)

And it all goes wrong.

If I include DATETIME_FORMAT for just one, eg “3 Month Start”, it shows that correctly, but puts ERROR in all other cells.

If I put DATETIME_FORMAT for both fields, it shows all cells as ERROR.

Argh!

Where am I going wrong, and how can I fix it?!

Also very grateful for advice if I’m making any other part of this unnecessarily complicated! :grinning_face_with_big_eyes:

thanks all

ScottWorld · ‎May 05, 2020

Yes, your formula is perfectly fine.

My guess is that you have some sort of invisible character embedded in there that is hanging up Airtable. I’ve noticed this sort of weird behavior in Airtable’s formulas before, but I can’t quite put my finger on what causes it.

Instead of copying & pasting the formula, just retype your entire formula from scratch, and it should work.

This exact formula worked for me (which is identical to your formula, but just using my fields) — but it DIDN’T WORK when I copied-and-pasted it into the formula cell. I had to retype all of it from scratch:

DATETIME_FORMAT({Date Time Field},‘DD MMMM YYYY’) & DATETIME_FORMAT({Date Time Field},‘DD MMMM YYYY’)

Hope this helps!

JonathanBowen · ‎May 05, 2020

Hi @Jessie_Jane - with a bit of tweaking I’m sure you can get the formula to work, but can I suggest an alternative approach?

If I was designing this base, rather than have different columns for different subscription start and end dates, I would have one start and one end date and use another field to note the subscription. The problem with the method you have above is that as soon as you want to add another subscription duration, you need to add two new columns for the start and end date and change the formula you are trying to write now.

One way to around this is to have a set-up like this:

Duration in months is a single select and the “End/Renewal Date” is a formula:

IF({Start Date}, DATEADD({Start Date}, {Duration in Months}, 'months'))

Now, if you want to add a 6 month subscription, then just add 6 as an option to the “Duration in Months” field and it will all work.

Even if you don’t want to add new subscriptions in the future, this is a neater way to structure your base.

(EDIT - sorry, I’ve formatted my date fields differently, but you get the idea :slightly_smiling_face: )

JB

JonathanBowen · ‎May 05, 2020