Oct 21 2022 10:02 PM
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
Oct 21 2022 10:13 PM - edited Oct 21 2022 10:14 PM
@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))