Skip to main content

Concatenate formula fields with formatted date/time

  • May 5, 2020
  • 5 replies
  • 137 views
J

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

This topic has been closed for replies.

5 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 5, 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
Forum|alt.badge.img+18

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
    Forum|alt.badge.img+18

    More like this

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • May 5, 2020

    Totally agree 100% with everything that @JonathanBowen said above!! He is 100% correct about that being the best & most efficient way to setup your database!

    @Jessie_Jane My answer above was SOLELY focused on your immediate question about why that one formula wasn’t working — I wasn’t focusing or paying attention to your database structure at all. But yes, @JonathanBowen is completely correct here. His way is the PROPER way to setup your database.

      J
      • Jessie_Jane
      • Author
      • New Participant
      • May 5, 2020

      Yassss thank you @ScottWorld and @JonathanBowen!

      I knew there had to be a smoother way about things :grinning_face_with_sweat:

      I’m stuck on mobile now, can’t mess about with the table properly at the minute. But I’m super psyched to get back into and sort things out as you suggest!

      Cheers guys :blush:

        Terms & ConditionsAccessibility statement