Is there any option for short date that is MMM/YY e.g Sep-21

Mike DeLuca

Currently have sheet that is in short date MMM/YY. We are trying to move this sheet across to smart sheet but an interim period we will still run Excel and copy back over that sheet so consistent format would make thing easy

Mike DeLuca

Thanks Bassam Khalil I have submitted the request. Makes sense for project management were month and year a higher priority.

Paul Newcome  Thanks Paul for your input. In short I would get away with first day of the month as its not really looked at.

Bassam Khalil

Hi @Mike DeLuca

Hope you are fine, Unfortunately, this feature is not currently available. You can submit a Smartsheet Product Enhancement Requests using this form.

but you can play around by using a helper column to convert the short date MMM/YY to long date dd/mmm/yyyy or any format you need.

if you want me to do it for you, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

The data will pull over, but it will pull over as a text value and not a date value. To convert it to a date in a separate column, there are a few different options. The best option would depend on your setup and a few details such as would you want all entries to be converted to the same day of the month such as the first or the fifteenth or would you want to be able to specify the actual day and just automate the year and month?

Mike DeLuca

Thanks Bassam Khalil I have submitted the request. Makes sense for project management were month and year a higher priority.

Paul Newcome  Thanks Paul for your input. In short I would get away with first day of the month as its not really looked at.

Paul Newcome

Ok. First we set up a nested IF statement for the months...

=IF(LEFT([Date Text Column]@row, 3) = "Jan", 1, IF(LEFT([Date Text Column]@row, 3) = "Feb", 2, IF(LEFT([Date Text Column]@row, 3) = "Mar", 3, ........................................))))))))))))

Keep the pattern going through until you have covered "Dec" = 12.

Next we can move on to the actual date formula that would go into a date type column.

=DATE(yyyy, mm, dd)

To pull the year...

=DATE(VALUE("20" + RIGHT([Date Text Column]@row, 2)), mm, dd)

The month we figured out with the nested IF statement:

=DATE(VALUE("20" + RIGHT([Date Text Column]@row, 2)), nested_if, dd)

=DATE(VALUE("20" + RIGHT([Date Text Column]@row, 2)), IF(LEFT([Date Text Column]@row, 3) = "Jan", 1, IF(LEFT([Date Text Column]@row, 3) = "Feb", 2, IF(LEFT([Date Text Column]@row, 3) = "Mar", 3, ........................................)))))))))))), dd)

And then the day is just 1.

=DATE(VALUE("20" + RIGHT([Date Text Column]@row, 2)), IF(LEFT([Date Text Column]@row, 3) = "Jan", 1, IF(LEFT([Date Text Column]@row, 3) = "Feb", 2, IF(LEFT([Date Text Column]@row, 3) = "Mar", 3, ........................................)))))))))))), 1)