Formula Issue

Cynde Ashcraft

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.

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), "")

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), "")

Cynde Ashcraft

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