HI

In column A I have a list of people's birthdays in dd/mm/yy format.

In column B I'm calculating their age using this formula :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
" & DATEDIF(A1,NOW(),"md") & " days"

This returns a string like

33 years, 9 months, 18 days

I am dragging this down to fill B1 - B25 and it works fine where the
cells in column A have content.

The question I have is this : How can I get the cells in column B to
remain blank where column A has no content? There are 25 spaces for
dates of birth in column A, but I'm only using 15 at the moment.

I'm not using A16 - A25 , so B16 - B25 should be blank until I do add
content. However , the formula in B16 - B25 is giving random ages when
nothing is showing in the equivalent cells in A.

Can any one help with a formula to have cells in B remain blank until A
has content?

Thanks.

Colin, like this,
=IF(A1="","",DATEDIF(A1,NOW(),"y")&" years, "&DATEDIF(A1,NOW(),"ym")&"
months,"&DATEDIF(A1,NOW(),"md")&" days")

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Colin Hayes" wrote in message
...

HI

In column A I have a list of people's birthdays in dd/mm/yy format.

In column B I'm calculating their age using this formula :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
" & DATEDIF(A1,NOW(),"md") & " days"

This returns a string like

33 years, 9 months, 18 days

I am dragging this down to fill B1 - B25 and it works fine where the
cells in column A have content.

The question I have is this : How can I get the cells in column B to
remain blank where column A has no content? There are 25 spaces for
dates of birth in column A, but I'm only using 15 at the moment.

I'm not using A16 - A25 , so B16 - B25 should be blank until I do add
content. However , the formula in B16 - B25 is giving random ages when
nothing is showing in the equivalent cells in A.

Can any one help with a formula to have cells in B remain blank until A
has content?

Thanks.

Try this:

=IF(N(A1),DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months, " & DATEDIF(A1,NOW(),"md") & " days","")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Colin Hayes" wrote:


HI

In column A I have a list of people's birthdays in dd/mm/yy format.

In column B I'm calculating their age using this formula :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
" & DATEDIF(A1,NOW(),"md") & " days"

This returns a string like

33 years, 9 months, 18 days

I am dragging this down to fill B1 - B25 and it works fine where the
cells in column A have content.

The question I have is this : How can I get the cells in column B to
remain blank where column A has no content? There are 25 spaces for
dates of birth in column A, but I'm only using 15 at the moment.

I'm not using A16 - A25 , so B16 - B25 should be blank until I do add
content. However , the formula in B16 - B25 is giving random ages when
nothing is showing in the equivalent cells in A.

Can any one help with a formula to have cells in B remain blank until A
has content?

Thanks.

One way:

B1: =IF(A1="","",<current formula)

In article ,
Colin Hayes wrote:

HI

In column A I have a list of people's birthdays in dd/mm/yy format.

In column B I'm calculating their age using this formula :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
" & DATEDIF(A1,NOW(),"md") & " days"

This returns a string like

33 years, 9 months, 18 days

I am dragging this down to fill B1 - B25 and it works fine where the
cells in column A have content.

The question I have is this : How can I get the cells in column B to
remain blank where column A has no content? There are 25 spaces for
dates of birth in column A, but I'm only using 15 at the moment.

I'm not using A16 - A25 , so B16 - B25 should be blank until I do add
content. However , the formula in B16 - B25 is giving random ages when
nothing is showing in the equivalent cells in A.

Can any one help with a formula to have cells in B remain blank until A
has content?

Thanks.

Hi

OK Thanks for your answers - all work perfectly. Much obliged for your
help and expertise.


Best Wishes


Colin





In article , JE
McGimpsey writes
One way:

B1: =IF(A1="","",<current formula)

In article ,
Colin Hayes wrote:

HI

In column A I have a list of people's birthdays in dd/mm/yy format.

In column B I'm calculating their age using this formula :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
" & DATEDIF(A1,NOW(),"md") & " days"

This returns a string like

33 years, 9 months, 18 days

I am dragging this down to fill B1 - B25 and it works fine where the
cells in column A have content.

The question I have is this : How can I get the cells in column B to
remain blank where column A has no content? There are 25 spaces for
dates of birth in column A, but I'm only using 15 at the moment.

I'm not using A16 - A25 , so B16 - B25 should be blank until I do add
content. However , the formula in B16 - B25 is giving random ages when
nothing is showing in the equivalent cells in A.

Can any one help with a formula to have cells in B remain blank until A
has content?

Thanks.

In article , Paul B
writes
Colin, like this,
=IF(A1="","",DATEDIF(A1,NOW(),"y")&" years, "&DATEDIF(A1,NOW(),"ym")&"
months,"&DATEDIF(A1,NOW(),"md")&" days")


Hi again

Just an extra thought :

As an extension to this , can this formula be extended to compare 2
cells?

In column A I have a date (dd/mm/yy) when people joined a scheme.

In column B I have the date (dd/m/yy) they left the scheme. If they are
still in the scheme , this is blank because they are still current.

Could I show in column C therefore how long in years months and days
they were in the scheme?

The formula would assume that if the cell in the column B were blank ,
then they are still in the scheme and would give length of time to NOW()
, If there is content in B that it needs to take one from the other to
give the length in C.

Can this be done via formula?

Grateful for any help.

Best Wishes


Colin

Colin, try this,

=IF(A1="","",IF(B1="",DATEDIF(A1,NOW(),"y")&" years,
"&DATEDIF(A1,NOW(),"ym")&" months,"&DATEDIF(A1,NOW(),"md")&"
days",DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&"
months,"&DATEDIF(A1,B1,"md")&" days"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Colin Hayes" wrote in message
...
In article , Paul B
writes
Colin, like this,
=IF(A1="","",DATEDIF(A1,NOW(),"y")&" years, "&DATEDIF(A1,NOW(),"ym")&"
months,"&DATEDIF(A1,NOW(),"md")&" days")


Hi again

Just an extra thought :

As an extension to this , can this formula be extended to compare 2
cells?

In column A I have a date (dd/mm/yy) when people joined a scheme.

In column B I have the date (dd/m/yy) they left the scheme. If they are
still in the scheme , this is blank because they are still current.

Could I show in column C therefore how long in years months and days
they were in the scheme?

The formula would assume that if the cell in the column B were blank ,
then they are still in the scheme and would give length of time to NOW()
, If there is content in B that it needs to take one from the other to
give the length in C.

Can this be done via formula?

Grateful for any help.

Best Wishes


Colin

In article , Paul B
writes
Colin, try this,

=IF(A1="","",IF(B1="",DATEDIF(A1,NOW(),"y")&" years,
"&DATEDIF(A1,NOW(),"ym")&" months,"&DATEDIF(A1,NOW(),"md")&"
days",DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&"
months,"&DATEDIF(A1,B1,"md")&" days"))


Hi Paul

Thanks very much for that - it's exactly what I needed. Works perfectly
first time.

Whatever they're paying you , it's not enough!


Best Wishes


Colin

Colin, glad I could help, just so you know nobody gets paid to answer any
questions here

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Colin Hayes" wrote in message
...
In article , Paul B
writes
Colin, try this,

=IF(A1="","",IF(B1="",DATEDIF(A1,NOW(),"y")&" years,
"&DATEDIF(A1,NOW(),"ym")&" months,"&DATEDIF(A1,NOW(),"md")&"
days",DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&"
months,"&DATEDIF(A1,B1,"md")&" days"))


Hi Paul

Thanks very much for that - it's exactly what I needed. Works perfectly
first time.

Whatever they're paying you , it's not enough!


Best Wishes


Colin

In article , Paul B
writes
Colin, glad I could help, just so you know nobody gets paid to answer any
questions here


Hi Paul

Yes - I did know that. It's just an expression...

^_^

Colin