IF function for returning month name with data that has zeros

willow1985

Active Member
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,821
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,821
Office Version
365
Platform
Windows
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;;
 
Last edited:

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
136
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,821
Office Version
365
Platform
Windows
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).
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
Thank you guys! This is great info :)
 

Forum statistics

Threads
1,085,950
Messages
5,386,893
Members
402,025
Latest member
saresum

Some videos you may like

This Week's Hot Topics

Top