Formula to check what month in another Cell, comes back as "1" on a Blank Cell

Occasional Contributor

So I'll describe this as I can't share the file.

 

In a Table, Cell B4 is for a Invoice entry #, When an invoice is entered it pulls the information from that Table. 

 

A Cell (J4) that pulls a Date from the Invoice #'s Tab has this Formula:

=IF($B4="","",INDIRECT($B4&"!"&$J$1))

 

Further to the right of the Table is a Cell (Q4) that checks for only the Month of that Date, A billing Date.

That Formula is :

=IFERROR(MONTH(J5)," ")

 

Without an invoice #, Everything stay blank, like I want.

 

The problem is, if that line in the Table has an Invoice #, but doesn't have a Date in that field from the Invoice Tab, the Billing date cell J4 stays Blank correctly, but somehow Cell Q4 returns a "1."

 

Cell J4 is set to show a Date, and Cell Q4, is set

1 Reply

@CADDmanDH If J5 is empty, the formula =MONTH(J5) will always return 1. An empty cell is seen as 0 (zero) when you use a function that expects a number/date. Enter the number 0 somewhere and format the cell as a date. You'll see that Excel returns January 0, 1900. Apply the MONTH function to that cell and you get 1 for the month of January.

 

To avoid this you need to use a similar check for an empty cell as you have in the other formula. So, something like =IF(J5="","",MONTH(J5))