Hiding a total until all values are filled

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DCZ - 27 Mar 2006 22:19 GMT

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?

Reply to this Message

Don Guillett - 27 Mar 2006 22:37 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.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
[quoted text clipped - 9 lines]
> Any
> Help?

Reply to this Message

Elkar - 27 Mar 2006 22:38 GMT

Try this:

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

HTH,
Elkar

> 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
[quoted text clipped - 6 lines]
> to Excel and I'm not sure what all options or tools are available to me. Any
> Help?

Reply to this Message

DCZ - 27 Mar 2006 23:32 GMT

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

> Try this:
>
[quoted text clipped - 13 lines]
> > to Excel and I'm not sure what all options or tools are available to me. Any
> > Help?

Reply to this Message

Don Guillett - 28 Mar 2006 00:15 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Elkar and Sandy, Dead on! That was exactly what I was after. Thank you so
> much.
[quoted text clipped - 24 lines]
>> > me. Any
>> > Help?

Reply to this Message

Sandy Mann - 27 Mar 2006 22:38 GMT

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

Signature

HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>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
[quoted text clipped - 9 lines]
> Any
> Help?

Reply to this Message