Skip to main content

Concatenate adding odd values

  • May 17, 2023
  • 7 replies
  • 34 views
Shannon_Bradley
Forum|alt.badge.img+15

Hey all! Not sure what I did, but I'm trying to create a simple field combining the values of 2 other fields.

Here is my formula:

And here is the results I am getting:

And I am trying to get it to retain Friendly date. Where are the extra values coming from, and why won't it keep friendly date?

* Second try *

My husband (who I have also converted to an AirTable lover) updated to this formula to remove those extra bits, but now it is adding a day to the displayed date? lol

This shouldn't be causing me this much trouble! lol

 

Thanks!

7 replies

R
Forum|alt.badge.img+7
  • RoseAmelia
  • Inspiring
  • 7 replies
  • May 17, 2023

Hey Shannon - 

Seems like you're already making good progress. The DateTime_Format Formula is how you'll want to format your dates and if you want more information on all the possible combinations take a look at this article.

This date glitch is odd. It seems like it was already adding an extra day in the first screenshot. I don't have any brilliant ideas, but since no one else has posted yet here are three ways I would start to debug.

1. Try just the DATETIME_FORMAT formula on the 'Version Date (From Script)'. Is it still giving you issues?

2. What is the original format of the 'Version Date' Field? Is it user-generated? Is it a Date field type?  Are you getting the same issue if you try to use the DATETIME_FORMAT formula in the Script table?

3. I've never had any issues (or heard of issues) with the Concatenate formula, but I'd also try using just '&' to concatenate the arguments and see if that makes a difference. 

Let me know if that begins to point you in the right direction or if you figure out the error! Good luck

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • May 17, 2023

Hi Shannon. Since your date is coming from a lookup field (second example - not sure why it changed from the first), I would try simple concatenation without the CONCATENATE formula.  Something like...

{Script} & " issued " & {Version Date (from Script)}

I haven't tried it but that's what I would try first.

Shannon_Bradley
Forum|alt.badge.img+15

Hi Shannon. Since your date is coming from a lookup field (second example - not sure why it changed from the first), I would try simple concatenation without the CONCATENATE formula.  Something like...

{Script} & " issued " & {Version Date (from Script)}

I haven't tried it but that's what I would try first.


That is the first thing we tried, and is what results in the bizarre addition of the extra values indicated in the first screenshot 🙂

Shannon_Bradley
Forum|alt.badge.img+15

Hey Shannon - 

Seems like you're already making good progress. The DateTime_Format Formula is how you'll want to format your dates and if you want more information on all the possible combinations take a look at this article.

This date glitch is odd. It seems like it was already adding an extra day in the first screenshot. I don't have any brilliant ideas, but since no one else has posted yet here are three ways I would start to debug.

1. Try just the DATETIME_FORMAT formula on the 'Version Date (From Script)'. Is it still giving you issues?

2. What is the original format of the 'Version Date' Field? Is it user-generated? Is it a Date field type?  Are you getting the same issue if you try to use the DATETIME_FORMAT formula in the Script table?

3. I've never had any issues (or heard of issues) with the Concatenate formula, but I'd also try using just '&' to concatenate the arguments and see if that makes a difference. 

Let me know if that begins to point you in the right direction or if you figure out the error! Good luck


1) Yes that didn't seem to change anything

2) Version date is a lookup from my scripts table and is a date field. The formula version date was an attempt to swap it to a standard value to keep the friendly date

3) Sadly no, it still gives those extra values at the end

Thank you for all this though!!!

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • May 18, 2023

Ok. One last idea. Use two functions - DATETIME_PARSE and DATETIME_FORMAT.

DATETIME_FORMAT(DATETIME_PARSE({date field as text like your formula field}, "MMMM D, YYYY"), "MMMM D, YYYY")

fingers crossed

Shannon_Bradley
Forum|alt.badge.img+15

Ok. One last idea. Use two functions - DATETIME_PARSE and DATETIME_FORMAT.

DATETIME_FORMAT(DATETIME_PARSE({date field as text like your formula field}, "MMMM D, YYYY"), "MMMM D, YYYY")

fingers crossed


Sadly that gave me errors, but I will keep playing with it. Shouldn't be this hard to keep friendly format! lol

 

Shannon_Bradley
Forum|alt.badge.img+15

Also no matter what I do it is still advancing by 1 day in the result. So weird!!

Have tried:

  • Using the date field directly
  • Running a simple formula to change it to straight text
  • Run the formula in the parent table then bringing it in as a lookup with straight text


Terms & ConditionsAccessibility statement