Changing MMDDYYYY date type column to YYYYMMDD format when referenced in another cell

The formula I currently have works to an extent, however, it omits the leading 0 on months and days. For instance, I get 2023-8-14 instead of 2023-08-14. Is there any way to add the zero?

Here's what I have right now:

=IFERROR(YEAR([Invoiced Date]@row) + "-" + MONTH([Invoiced Date]@row) + "-" + DAY([Invoiced Date]@row) + " " + Make@row + " " + Model@row + " " + [Stock#]@row, "")

Find more posts tagged with

Accepted answers

JamesB

@RossG

This formula will check if the Invoiced Date isblank and leave the cell blank if true. IF false it will check the Month and Date formulas to be less then 2 characters long and add the leading 0 if needed.

=IF(ISBLANK([Invoiced Date]@row), "", YEAR([Invoiced Date]@row) + IF(LEN(MONTH([Invoiced Date]@row)) < 2, "-0", "-") + MONTH([Invoiced Date]@row) + IF(LEN(DAY([Invoiced Date]@row)) < 2, "-0", "-") + DAY([Invoiced Date]@row))

All comments

JamesB

@RossG

RossG

It's a miracle! I would have never figured that out! 😁 Thank you

JamesB

Your Welcome.

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.