IF function for returning month name with data that has zeros

[willow1985]

Hello,

I have a column that has a list of "0" and dates.

I am looking for a formula that will return the Month name on any cell that does not contain the text "0" and if it has a "0" return a blank cell.

Here is the formula I wrote but unfortunately it does not seem to be working.
=IF(M2<>"0",TEXT(M2,"mmmm"),"")

Data looks like this:

0
2019-09-23
2019-09-02
2019-08-05
0
2018-05-07

The 0 is "number stored as text".

If I change the formula to: =IF(M2<>0,TEXT(M2,"mmmm"),""), it returns the month name as January for every cell and if I have it as "0" it returns everything as blank.

Hope someone can help me with this

Thank you

Carla

 

[Joe4]

It sounds like your entries are Text and not Date.

You can convert them all to Dates by using "Text to Columns", choosing the Date option, with the YMD format option.
Then your last formula should work.

 

[willow1985]

I figured out my error. This formula works but if anyone can let me know if there is a more efficient one that would be greatly appreciated. The data will always come in with zeros...

 

[Joe4]

This formula works but if anyone can let me know if there is a more efficient one that would be greatly appreciated.

There are certainly other ways to do it, though I don't know if they would be any more efficient. That is pretty much the method I would choose if I needed to get the month name in Text format.

Note that if you just want to change the way that column is displayed without adding extra columns or formulas, you could just apply a Custom Format on that column of:
mmmm;;

 

[ClaireS]

You could set the custom format of your date column to yyyy-mm-dd;; which will display dates as dates and zero values as 0. Then your date column will look the way you want while still being numerical dates. Then your second formula will work. You will need your TEXT function if you are referencing the name of the month anywhere else as text.
But if you just need to see the name of the month in the next column and aren't using it elsewhere, you could use: =IF(M2>0,M2,""), and custom format that column as mmmm.

 

[Joe4]

You could set the custom format of your date column to yyyy-mm-dd;; which will display dates as dates and zero values as 0.

Note that changing the format on already entered the data will NOT change the nature of it. So, if it was entered as text, changing to a date format will not change it, unless you re-enter the data (or do a Text to Columns on it).

 

[ClaireS]

Good point!

 

[willow1985]

Thank you guys! This is great info