Convert date format to mmm-yy

Bill Lafferty

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.

 

 

Bill Lafferty

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

 

KrisWalsh

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

KrisWalsh

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

JohnHinkle

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

Bill Lafferty

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

Bill Lafferty

Love it!  Worked great!  Thanks.

 

KrisWalsh

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