Concatenate and summarize each rollup value

[Shannon_Bradley]

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!

[RoseAmelia]

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

[augmented]

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]

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]

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!!!

[augmented]

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]

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

[Shannon_Bradley]

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