Change Date Format Not Working

[shapenote]

Have a spreadsheet where the date format appears as 21/03/2018.

I am trying to change it to 21-Mar-2018, but nothing I do works. I have tried copying the date to a blank cell and formatting, I have tried Date formula, Datevalue, and several other tricks I know.

It doesn't appear the original input is text -- so I don't know what the issue could be.

Any thoughts/tips???

 

[jtakw]

Hi,

Appearance can be deceiving, assuming data in F1, what result do you get with =ISNUMBER(F1)

This might work:

Book1
	F	G	H
1	21/03/2018		Mar-21-2018
Sheet38

Cell Formulas
Range	Formula
H1	=DATE(RIGHT(F1,4),MID(F1,FIND("/",F1)+1,2),LEFT(F1,FIND("/",F1)-1))

Format result cell as mmm-dd-yyyy

 

[shapenote]

Jtakw,

The =ISNUMBER(F1) give me a "False" return. So I guess it really is a text.

The formula you gave me works!!!! Thanks a million!

 

[jtakw]

You're very welcome.