#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Convert date format to mmm-yy

Options
✭✭✭✭
edited 12/09/19

Does anyone know how to convert a date in dd/mm/yy format to a mmm-yy format with a formula like =TEXT([Start Date],"mmm-yy")?
Thanks.

Tags:

• ✭✭✭✭
edited 02/20/16
Options

Thank you.  Your solution mirrors some additional feedback from some of the SmartSheet  staff had given me as well.  Sharing the full details below.

For the purpose of this exercise, add 4 columns to the right of the Start Date, for this example, they are called Formula1 (month text), Formula2 (date into text), Formula 3 (yr), and Formula 4 (result).

Assume the Start Date field is in the mm/dd/yyyy format.

In the 1st column called 'Formula1 (month text)' use the formula above to get the month data in the "mmm" format.

=IF(ISDATE([Start Date]1), (IF(MONTH([Start Date]1) = 1, "Jan", IF(MONTH([Start Date]1) = 2, "Feb", IF(MONTH([Start Date]1) = 3, "March", IF(MONTH([Start Date]1) = 4, "Apr", IF(MONTH([Start Date]1) = 5, "May", IF(MONTH([Start Date]1) = 6, "June", IF(MONTH([Start Date]1) = 7, "July", IF(MONTH([Start Date]1) = 8, "Aug", IF(MONTH([Start Date]1) = 9, "Sept", IF(MONTH([Start Date]1) = 10, "Oct", IF(MONTH([Start Date]1) = 11, "Nov", IF(MONTH([Start Date]1) = 12, "Dec", ""))))))))))))))

In the next column, 'Formula2 (date into text)', use the formula below to add spacing to the Start Date

=[Start Date]1 + ""

In the 3rd column, we are going to trim the Year data and create the "yy" format.

=RIGHT([Formula2 (date into text)]1, 2)

In the last column, I would connecate the results of the 2nd & 3rd column as follows to yield the "mmm-yy" format.

=IF(ISDATE([Start Date]1), [Formula1 (month text)]1 + "-" + [Formula 3 (yr)]1, "")

• ✭✭✭✭✭
Options

SDate is the reference column name.

=IF(MONTH(SDate1) = 1, "Jan-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 2, "Feb-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 3, "Mar-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 4, "Apr-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 5, "May-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 6, "Jun-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 7, "Jul-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 8, "Aug-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 9, "Sep-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 10, "Oct-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 11, "Nov-"+ MID(YEAR(SDate1), 3, 2), IF(MONTH(SDate1) = 12, "Dec-"+ MID(YEAR(SDate1), 3, 2), ""))))))))))))

If your reference column name has a space it would look like this.

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

• ✭✭✭✭✭
Options

There's more than one way to skin a cat.  (:

• ✭✭✭✭✭✭
Options

You could also use this which would require only one column (using LEFT, rather than MID):

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

• ✭✭✭✭
Options

Thanks John.  Less is more.  Let me give this a shot.

• ✭✭✭✭
edited 02/22/16
Options

Love it!  Worked great!  Thanks.

• ✭✭✭✭✭
Options

Good call, John!  I've hardly used LEFT & RIGHT.  I'm going to start now!  TY!

This discussion has been closed.