Change date format

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