- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 10, 2021 12:36 PM
Hi community,
I have the following problem and my solution doesn’t work Could you share your advice, please?
I have an automation that delivers a date field in the following format YYYY-MM-DD. However, this is not useful for EU users so I tried to change the format by creating a new formula field that converts the date information to the format I want. The problem is that in Gmail automation that sends the mail with information, the new field (with formula) is blank…
What could I do about it?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 10, 2021 08:52 PM
Welcome to the community, @Dimitris_Goudis! :grinning_face_with_big_eyes: Could you please share the formula that you’re using to change the date format, and also share screenshots of your automation setup? This will make it a lot easier to figure out what’s happening.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 11, 2021 08:16 AM
Thanks for your welcoming message.
Below I have the screenshot of the automation (when a user clicks on a checklist, then an email has to be delivered to a specific mail address. This mail includes the field I have the issue.)
The problem is in the field “Ημερομηνία Παράδοσης”. This field is a lookup field that parses a date from another table. The parsed field is a formula field with the following function that converts the date into the willing format.
DAY({Ημερομηνία Παράδοσης})&" - “&
SWITCH(MONTH({Ημερομηνία Παράδοσης}),1,“Ιανουάριος”,2,“Φεβρουάριος”,3,“Μάρτιος”,4,“Απρίλιος”,5,“Μάιος”,6,“Ιούνιος”,7,“Ιούλιος”,8,“Αύγουστος”,9,“Σεπτέμβριος”,10,“Οκτώμβριος”,11,“Νοέμβριος”,12,“Δεκέμβριος”)&” - "&YEAR({Ημερομηνία Παράδοσης})
- the “Ημερομηνία Παράδοσης” is the date field I want to convert.
- I use the switch in order to have the text result in MONTH() function in Greek language.
- below is a screenshot of the function result
- When I parse in the mail the original value it comes with the following format (YYYY-MM-DD) and this is the main problem I have faced
Thanks again !!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 24, 2021 09:47 PM
Sorry for the delayed reply. If I understand you correctly, you’re saying that inserting the formula field into the email sometimes doesn’t work, resulting in a blank space where the date is supposed to be. Is that correct? That’s what I gather from your original question. However, in your latest reply you said:
The reason that you’re getting that format by directly inserting the date is because the formatting that you see in the field is only for display purposes. To display that date anywhere else, you’ll typically use the DATETIME_FORMAT()
function. Another function that might be useful is the SET_LOCALE()
function, which is used together with DATETIME_FORMAT()
to create a formatted date with specific locale (language) options. While the function that you wrote will also work, I suggest checking out the details in the documentation about both of these functions: