Forum Discussion

lisaa100's avatar
lisaa100
Copper Contributor
May 17, 2021
Solved

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

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!

excel

6 Replies

  • hsotnas's avatar
    hsotnas
    Copper Contributor
    Jan 30, 2023

    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

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 17, 2021

    lisaa100 

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

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

     

    Fill down.

    • RichardDJenkin's avatar
      RichardDJenkin
      Copper Contributor
      Oct 25, 2024

      HansVogelaar Good morning.

      I have a similar problem, but I am no computer expert and a bit of a thickie to be honest!

      A1 is a date of entry, K1 is a 7 day follow up (formula used is =A1+7) M1 is 14 day follow up (formula used is =K1+7) All this works, but a default date shows in all K and M columns and when I clear contents, it erases the formula and I hate having dates showing on empty rows!

      I am a dinosaur and do not understand Excel terminology.

      Are you able to help an old man drowning in tech speak please?

       

      Rich

Resources