=month() function returns value of 1 on blank cell

**neeners36** · 03-28-2012, 03:54 PM

I have two columns: one is for date and the other uses the =month() function to pull the number of the month. The spreadsheet will be filled out by other users so the date column is not yet populated. The =month() function is returning a value of 1 from a cell that is blank. How do I get the =month() cell to be blank or return a value of 0 until the date field is populated?

**NBVC** · 03-28-2012, 03:56 PM

Try:

=IF(A1="","",MONTH(A1))

**tom1977** · 03-28-2012, 03:58 PM

try this
=IF(ISBLANK(A1),0,MONTH(A1))

**neeners36** · 03-28-2012, 03:59 PM

Thank you very much.

**Haseeb Avarakkan** · 03-28-2012, 05:30 PM

You can also use TEXT function,

=TEXT(A1,"m;;;")

if A1 is Negative, zero, blank or text will show as blank.

**Paul** · 03-28-2012, 06:01 PM

So you're aware of *why* it's returning 1 (January).. it's because dates in Excel are actually stored and processed as numbers starting with January 1, 1900. The number for that date is 1. Today, March 28, 2012, is number 40996, since it is the 40,996th day since "the beginning of time" (January 1, 1900).

When you have a blank cell (or 0 in a cell) and test it using certain date functions, it will return - oddly - January 0, 1900. It's not a real date, of course, but Excel considers it to be in January.

**Antoneo** · 10-21-2019, 07:38 PM

Awesome. I won't have figured that out that easy. Thanks

Originally Posted by Haseeb A

You can also use TEXT function,

=TEXT(A1,"m;;;")

if A1 is Negative, zero, blank or text will show as blank.

**offbyone** · 12-14-2019, 03:47 AM

Thanks so much, Haseeb - that worked like a charm!

Can you please explain why the three trailing semicolons hide negative, zero, or blank text? Microsoft's TEXT function documentation does not provide any clues (sadly, I cannot post a link to it due to forum restrictions on new users).

In any case, it's just what I was after to prevent "January 0, 1900" from appearing when evaluating a blank cell.

Thank you again!

**Pepe Le Mokko** · 12-14-2019, 04:06 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

**offbyone** · 12-14-2019, 01:45 PM

Originally Posted by Pepe Le Mokko

Welcome to the forum.

Thank you for your kind greeting! And thanks for your work in keeping this forum together.

Originally Posted by Pepe Le Mokko

whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

I'm sorry if my question wasn't clear; I am not adding anything unique, just asking why the three semicolon solution had worked; I couldn't find any reference to such a convention anywhere else.

**AliGW** · 12-14-2019, 01:49 PM

You should still start a new thread - just make reference to this one. Your thread title can relate directly to the use of the three semi-colons. Please do as you have been asked. Thanks.