Hiding a total until all values are filled

[DCZ]

I want to forcast certain dates based on a persons age. For example, my
daughter was born on 11-5-96. I want to forcast her 8th, 12th, and 18th
birthday. I am wanting to do this for a hundred or so youth at our church.
So, I put in the following =DATE(YEAR(B1)+8,MONTH(B1),DAY(B1)) in A1. The
problem I have is I have an incorrect date in A1 until a date is entered.
Excel wants to put in 31-Dec-07 if B1 is blank. How can I keep A1 blank until
I have entered B1 without entering it all in one at a time, the whole of
column A will have the same equation with regards to its row. I am just new
to Excel and I'm not sure what all options or tools are available to me. Any
Help?

 

[Don Guillett]

try
=if(or(b1="",b1=" "),"",yourformula)
or
if(len(b1)<2,"",yourformula)

 

[Elkar]

Try this:

=IF(B1="","",DATE(YEAR(B1)+8,MONTH(B1),DAY(B1))

HTH,
Elkar

 

[Sandy Mann]

If B1 is empty Excel values it as zero. Date zero to Excel is 00 - Jan -
1900 so eight years on from that is 00 - Jan - 1908. Of course there is no
day 00 in Jan 1900 so Excel gives you the day before 1/1/1900 which is 31 -
Dec - 1907

Now to cure this add a test for an empty cell:

=IF(B1="","",DATE(YEAR(B1)+8,MONTH(B1),DAY(B1)))

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[DCZ]

Elkar and Sandy, Dead on! That was exactly what I was after. Thank you so much.
DCZ

 

[Don Guillett]

If you are using this just be sure you don't inadvertently touch the
spacebar