IF function for returning month name with data that has zeros

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. 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:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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...
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top