Hiding a total until all values are filled

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?

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"DCZ" <DCZ@discussions.microsoft.com> wrote in message
news:5D501785-8639-4C0D-AF5F-A3086B572536@microsoft.com...
>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?

Try this:

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

HTH,
Elkar


"DCZ" wrote:

> 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?

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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"DCZ" <DCZ@discussions.microsoft.com> wrote in message
news:5D501785-8639-4C0D-AF5F-A3086B572536@microsoft.com...
>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?

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

"Elkar" wrote:

> Try this:
>
> =IF(B1="","",DATE(YEAR(B1)+8,MONTH(B1),DAY(B1))
>
> HTH,
> Elkar
>
>
> "DCZ" wrote:
>
> > 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?

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"DCZ" <DCZ@discussions.microsoft.com> wrote in message
news:5986A611-413D-4D90-9791-693F4286E194@microsoft.com...
> Elkar and Sandy, Dead on! That was exactly what I was after. Thank you so
> much.
> DCZ
>
> "Elkar" wrote:
>
>> Try this:
>>
>> =IF(B1="","",DATE(YEAR(B1)+8,MONTH(B1),DAY(B1))
>>
>> HTH,
>> Elkar
>>
>>
>> "DCZ" wrote:
>>
>> > 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?