Empty Date Cell shows as 1/0/1900, but want it blank

New Contributor

Hi there!


I'm a novice, so bear with me. Numbered items below in green are working just fine, items in red are what I'm having issues with:


  1. L7 has formula =N7-M7
  2. However, I want for cells in that column to be blank when N7 and/or M7 are empty.
  3. Then, when user enters the values for N7 and M7, it provides calculated dates for L7 and J7.
  4. Cells with calculated dates are green if 30+ days from Today, yellow if less than 30 days from Today, and red if Today or past Today.


My sheet is heavily involved with conditional formatting, so I've attached that picture as well just in case it has anything to do with that.


However, I'm thinking this is something simple with Number Formatting>Date and seeing that the sample reads as "1/0/1900".


Any help is greatly appreciated!

2 Replies
best response confirmed by allyreckerman (Microsoft)


In L5:  =IF(COUNTA(M5:N5)<2,"",N5-M5)

In J5:   =IF(COUNTA(K5,N5:M5)<3,"",N5-M5-K5)


Fill down.

Sweet! That worked, thanks a bunch!