Change date format

[Edward Holden]

Hi all!

I want to change a bunch of dates that look like the below into a simple 'Nov-16' format.

How do I do this?

[#2]

Hi Edward,

You have some texts which can't be changed just by formatting, you need to transform your texts. It could be several ways, perhaps the easiest one is to use Flash Fill - type manually in next to the right columns texts as you desire to see it, e.g.

7-Nov-16 - 13-Nov-16

After the couple of cells Flash Fill will suggest to fill the column till end, press Enter. Or select cells at this at new column starting from ones you have texts till end of the range and Home->Fill-Flash Fill.

Otherwise you may generate you texts from scratch in any format adding week number as helper column; or split your column on two by Data->Text to columns and combined back with desired format; or parse the text and combine parts with desired format.

[#3]

Dear Edward Holden,

Kindly refer to the formulas below, and use the one that is most appropriate to your requirement.

The period that I have used in the examples is from February 27, 2017 - March 5, 2017

1. Formula will evaluate the value of the first date. Change the cell format to "MMM-YY". 

Result will be numerical.

=VALUE(LEFT(A1,FIND("-",A1:A1)-1))

Result: 42793

Displayed as: Feb-17 after formatting cell.

 

2. Formula will evaluate the value of the second date.

Change the cell format to "MMM-YY".

Result will be numerical.

=VALUE(MID(A1,FIND("-",A1,1)+2,20))

Result: 42799

Displayed as: Mar-17 after formatting cell.

 

3. Result will be the same Text as you have entered, but, displayed in the desired way.

Non-Numerical Result.

=TEXT(VALUE(LEFT(A1,FIND("-",A1:A1)-1)),"MMM-YY")&" - "&TEXT(VALUE(MID(A1,FIND("-",A1,1)+2,20)),"MMM-YY")

Result: Feb-17 - Mar-17

 

4. Display the First Date as Text in the desired format.

=TEXT(VALUE(LEFT(A1,FIND("-",A1:A1)-1)),"MMM-YY")

Result: Feb-17

5. Display the second date as text in the desired format.

=TEXT(VALUE(MID(A1,FIND("-",A1,1)+2,20)),"MMM-YY")

Result: Mar-17

Notes:

1. Change the cell references as required.

2. When desired formats are not available in standard Number Format, use Custom Format.

3. For Numerical results changing the format of the cell is important. For 27 Feb 2017, the numerical value of the cell is 42793.

I hope this solves your problem.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India