Concatenate and DATETIME_FORMAT

[Max-Antoine_EDI]

Hi Everyone !

I have trouble using the concatenate formula with datefield. My concatenate is supposed to be “Training name - Beginning date - Ending date”

My dates format are displayed as european and doesn’t include time. When I use the concatenate, it shows this : “activité ludique & éveil sensoriel - 2019-01-19T00:00:00.000Z” - The last part shouldn’t appear, and the date format is wrong. I read Airtable guide on DATETIME_FORMAT configuration, however it just doesn’t work because of an “Invalid formula”, and I don’t know if it’s because of the way i’m writing it…

I’ve tried like this :

• CONCATENATE({Formation} & " - " & DATETIME_FORMAT(Date, ‘DD-MM’){Date de début})
• CONCATENATE({Formation} & " - " & DATETIME_FORMAT({Date de début}, ‘DD-MM’)
  … And various combination of {} & () :grinning_face_with_smiling_eyes:

Problem 1 : make the concatenate work without time :grinning_face_with_big_eyes:
Problem 2 : I’ve displayed time in european format but in my concatenate it doesn’t display the same way :frowning:

Ps1 : “Date de début” correspond to “beginning date” field (it is configured as a date field)
Ps2 : I only included “Date de début” to exemplify my problem, however i’d wish my concatenate to include the beginning date and the ending date (which are in two separated field)

Hope this was clear enough, thank you all for your time and your help :slightly_smiling_face:

[W_Vann_Hall]

1. You’re using both forms of concatenation which, while not incorrect, can be awfully confusing. I prefer the shorthand method of using the ampersand (’&’) and ignoring the explicit CONCATENATE() function entirely.

2. I am assuming none of your date values are being accessed as a lookup or rollup field — that is, they are data entry or formula fields in the table containing the formula. I’m also assuming the issue regarding the formatting of the time value isn’t actually a problem [in this case], as you earlier seem to indicate you wish the final formula to return date-only values.

   If these assumptions are correct, your formula should be

   {Formation}&' - '&
   DATETIME_FORMAT({Beginning date},'DD-MM')&' - '&
   DATETIME_FORMAT({Ending date},'DD-MM')
   

(I broke that across three lines for clarity’s sake; it can be copy-and-pasted directly [Airtable will accept line breaks and indentation], if I’d only remembered to use your actual field names :winking_face: or entered as a single line.)

For completeness’ sake, I think the equivalent using the CONCATENATE() function would read

CONCATENATE(
    {Formation},
    ' - ',
    DATETIME_FORMAT({Beginning date},'DD-MM'),
    ' - ',
    DATETIME_FORMAT({Ending date},'DD-MM')
    )

[Mics_Sky]

Max-Antoine_EDIGHOFF:

Try this:

CONCATENATE(Formation , " - " , DATETIME_FORMAT({Date de début}, ‘DD-MM’), " - " , DATETIME_FORMAT({Date de fin}, ‘DD-MM’))
I presume that

• “Formation” is the name your training course field.
• “Date de début” is the name your Beginning date field.
• and maybe “Date de fin”, is the name your ending date field.
• Fields are separated by a comma, “virgule” in french !

should work properly!

[Max-Antoine_EDI]

It works perfectly, thank you so much !

Thanks for the first tips too @W_Vann_Hall, didn’t know I could juste use “&”. You’re assumptions were all correct :winking_face: