Formula Issue

I'm trying to write a formula that will turn a date format of MM/DD/YY into Mon-YY (for example, 02/19/25 into Feb-25, or if the date cell is blank, then I just want a blank returned. Here's what I have :

=IF(MONTH([CLEAR Approval Date]@row) = 1, "Jan-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 2, "Feb-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 3, "Mar-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 4, "Apr-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 5, "May-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 6, "Jun-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 7, "Jul-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 8, "Aug-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 9, "Sep-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 10, "Oct-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 11, "Nov-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), IF(MONTH([CLEAR Approval Date]@row) = 12, "Dec-" + RIGHT(YEAR([CLEAR Approval Date]@row), 2), ""))))))))))))

Everything works as it should UNLESS the CLEAR Approval Date@row is blank, in which it returns an error of #INVALID DATA TYPE. Please help - I have not been able to fix this.

Find more posts tagged with

Accepted answers

Leibel S

@Cynde Ashcraft

See below, i added in the beginning an IF statement to check for a date, then i also changed your formula to make it a bit smaller by adding the year at the end only one time…

=IF(ISDATE([CLEAR Approval Date]@row), IF(MONTH([CLEAR Approval Date]@row) = 1, "Jan-", IF(MONTH([CLEAR Approval Date]@row) = 2, "Feb-", IF(MONTH([CLEAR Approval Date]@row) = 3, "Mar-", IF(MONTH([CLEAR Approval Date]@row) = 4, "Apr-", IF(MONTH([CLEAR Approval Date]@row) = 5, "May-", IF(MONTH([CLEAR Approval Date]@row) = 6, "Jun-", IF(MONTH([CLEAR Approval Date]@row) = 7, "Jul-", IF(MONTH([CLEAR Approval Date]@row) = 8, "Aug-", IF(MONTH([CLEAR Approval Date]@row) = 9, "Sep-", IF(MONTH([CLEAR Approval Date]@row) = 10, "Oct-", IF(MONTH([CLEAR Approval Date]@row) = 11, "Nov-", IF(MONTH([CLEAR Approval Date]@row) = 12, "Dec-", "")))))))))))) + RIGHT(YEAR([CLEAR Approval Date]@row), 2), "")

All comments

Leibel S

@Cynde Ashcraft

See below, i added in the beginning an IF statement to check for a date, then i also changed your formula to make it a bit smaller by adding the year at the end only one time…

Cynde Ashcraft

@Leibel S - THANK YOU SO MUCH - This works beautifully :)

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