SOLVED

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

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

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@lisaa100 

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!

@lisaa100 

 

Hi, You can change the format of the cells by going to Format Cells > Customs and adding following

[=0] ;dd-mm-yyyy

 

This will display a blank wherever the result is 0

 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@lisaa100 

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

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

 

Fill down.

View solution in original post