Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper 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:



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 :



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))