Nesting MID Function within =IF; Not able prevent excel from removing zero from formula

[Stephen Buttle]

Hi there,

I'm trying to get excel to calculate a date function using =Mid nested within an =IF formula. What I want it to do is to return the month value as name (e.g Jun nd not 06). However I've run into an issue that so far I've not been able to solve. Excel keeps removing the zero in front of the month to incorrectly return a false answer.

I have tried;

Altering the Mid formula to starting searching 1 character before the zero (and increasing the last criteria by 1) in the hope that it would trip excel into remembering to keep the zero; problem is that digit is a hypen so excel thinks its a minus figure which is why excel continues to remove the zero).

looking at formatting cell option to change the format of the date in the source cell. At the moment I can't get the source cell to do this.

I am using excel 365. Can anyone help me with this?

[DavidBowman]

The trick is to quit using text functions and actually convert the original text to a date by using the datevalue function. Then you can use all of the other date functions to get the values you want. See attached.

[sohaila]

=TEXT(DATEVALUE(E2),"mmm")