Convert Created Date to Month/Date using Local Time

We have a "Audit date" column which collects the date and time when a row is added. That currently comes through as "6/30/25 5:15 PM".

We have a formula that labels a Month / Year for reporting based on the "Created Date" column. That formula currently is

=YEAR([Audit Date]@row ) + " / " + MONTH([Audit Date]@row )

This has been working fine 90% of the time, except for the last day/first day of the month.

I did some reading in the community and think that the issues is UTC time. I tried to just manually change those data entries to 2025 /6 but it stopped applying the formula to the rest of the column.

I need it in Eastern Time. I tried to follow some solutions but I think the AM/PM is differing from others and making their solutions not work for me.

Find more posts tagged with

Accepted answers

KPH

Hi @ctaft

It sounds like you are almost there.

How about using this to extract the month:

=LEFT([Audit Date]@row , 2)

It means look at Audit Date and return the first two characters.

Then for the year:

=MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2)

This means look at Audit Date, FIND the first /, count 4 characters from there (with the / being 1, mm being 2 and 3, and / being 4), return the next 2 characters.

You would then combine these to get the format you want:

="20" + MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2) + " / " + LEFT([Audit Date]@row , 2)

Hope this helps.

All comments

KPH

Hi @ctaft

It sounds like you are almost there.

How about using this to extract the month:

=LEFT([Audit Date]@row , 2)

It means look at Audit Date and return the first two characters.

Then for the year:

=MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2)

This means look at Audit Date, FIND the first /, count 4 characters from there (with the / being 1, mm being 2 and 3, and / being 4), return the next 2 characters.

You would then combine these to get the format you want:

="20" + MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2) + " / " + LEFT([Audit Date]@row , 2)

Hope this helps.

ctaft

This worked, thank you so much!

KPH

Wonderful. Thanks for letting me know.

ctaft

@KPH Is there a way to drop the leading zero on the month? I have dashboards and reference sheets set up to look for "2025 / 7" not "2025 / 07"

KPH

You can add an IF function and use a MID function for months that start with 0, and the existing LEFT for the rest.

="20" + MID([Audit Date]@row , FIND("/", [Audit Date]@row ) + 4, 2) + " / " + IF(LEFT([Audit Date]@row , 1) = 0, MID([Audit Date]@row , 2, 1), LEFT([Audit Date]@row , 2))

The parts in bold are new (including the extra paranthesis at the end). They mean:

If the first character in the string is 0

IF(LEFT([Audit Date]@row , 1) = 0

Extract 1 character at position 2.

MID([Audit Date]@row , 2, 1)

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, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.