SOLVED

Empty date cells shows as 01/01/1900, but I want them left blank

%3CLINGO-SUB%20id%3D%22lingo-sub-3063088%22%20slang%3D%22en-US%22%3EEmpty%20date%20cells%20shows%20as%2001%2F01%2F1900%2C%20but%20I%20want%20them%20left%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3063088%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20a%20beginner%2C%20so%20please%20be%20patient!%20I%20have%20created%20a%20simple%20spreadsheet%20with%20names%20and%20dates%3A%3C%2FP%3E%3CP%3EF3%20is%20the%20formatted%20to%20dd%2Fmm%2Fyy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20color%3D%22%23000000%22%3EH3%20has%20formula%20%3DF3%2B7%20(1%20week)%2C%20J3%20has%20the%20formula%20%3DF3%2B14%20(2%20weeks)%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CFONT%20color%3D%22%23000000%22%3EIf%20F3%20is%20empty%20the%20following%20is%20shown%20in%20H3%20and%20J3%2007%2F01%2F00%20and%2014%2F01%2F00%20respectively%3C%2FFONT%3E%3CFONT%20color%3D%22%23000000%22%3E%2C%20but%20I%20want%20these%20cells%20to%20be%20blank%20when%20F3%20is%20empty.%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CFONT%20color%3D%22%23000000%22%3EThen%2C%20when%20someone%20enters%20a%20date%20in%20F3%2C%20it%20provides%20calculated%20dates%20for%20H3%20%26amp%3B%20J3%3C%2FFONT%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20want%20to%20add%20some%20conditional%20formatting%20so%20that%20these%20highlight%20red%20when%20they%20are%20due%2Foverdue.%26nbsp%3B%20Thinking%20of%20making%20the%20text%20white%20then%20(highlight%20cell%20rules%20%26gt%3B%20less%20than%20todays%20date)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EThis%20is%20driving%20me%20mad!%26nbsp%3B%20I've%20looked%20at%20other%20similar%20questions%2C%20but%20they%20all%20seem%20too%20complex.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3063088%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi!

 

I'm a beginner, so please be patient! I have created a simple spreadsheet with names and dates:

F3 is the formatted to dd/mm/yy

 

  • H3 has formula =F3+7 (1 week), J3 has the formula =F3+14 (2 weeks)
  • If F3 is empty the following is shown in H3 and J3 07/01/00 and 14/01/00 respectively, but I want these cells to be blank when F3 is empty.
  • Then, when someone enters a date in F3, it provides calculated dates for H3 & J3

 

I then want to add some conditional formatting so that these highlight red when they are due/overdue.  Thinking of making the text white then (highlight cell rules > less than todays date)

 

This is driving me mad!  I've looked at other similar questions, but they all seem too complex.

 

Any help is greatly appreciated!

2 Replies
best response confirmed by Robo100 (New Contributor)
Solution

@Robo100 Perhaps this?

=IF(F3="","",F3+7)

 

That is great! Thank you SO much!!