Concatenate formula to combine Date and Time into unique code

When I use a concatenate formula to combine a Date and Time to create an individual code, the Date appears in the default format 01/01/25 but the date in the Column it is referencing is formatted 2025-01-01 which is how I need it to appear in the result. How can get the Concatenate formula to just reproduce the Date format from the cell it is referencing? I need the result to be Date + "T" + Time + Z and appear as 2025-01-01T06:00Z for example. Currently no matter what I try the result comes up as 01/01/25T06:00Z

The UTC Start Date Column is looking at another Date Column and calculating the correct UTC Date based on the Time difference between Central Time and UTC time. That column is formatted in the Default 01/01/25 format (See second photo)

Here is the original column that the formulas are referencing below

Screenshot 2025-03-19 at 10.01.45 AM.png

Here you see the UTC Start Date Column has converted the date based on time of day, and the format is correct. Any idea on how to get the result in UTC Start Date/Time to display this way? 2025-01-01T06:00Z

I have tried to make UTC Start Date/Time column a Date column and change the formatting to no avail. I Tested changing the format of the original source column "Date" to the 2025-01-01 format but my Concatenated result was still formatted incorrectly…

Find more posts tagged with

Formulas

formatting

dates

Accepted answers

Soumitra Bhowmick

Try this

=YEAR([UTC Start Date]@row) + "-" + IF(MONTH([UTC Start Date]@row) < 10, 0, "") + MONTH([UTC Start Date]@row) + "-" + IF(DAY([UTC Start Date]@row) < 10, 0, "") + DAY([UTC Start Date]@row) + "T" + [UTC Start Time]@row + "Z"

All comments

Soumitra Bhowmick

Try this

DMCARBRAY

That is AWESOME!!!!

Thank you so much for that input. I had a similar problem in the past that involved a similar solution related to date display but could not translate that formula and make it work. Thank you again!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.