How to leave a formula cell blank when it has no calculation to make

[Colin Hayes]

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.

 

[Paul B]

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

 

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[Colin Hayes]

Hi

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


Best Wishes


Colin

 

[Colin Hayes]

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

 

[Paul B]

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]

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

 

[Paul B]

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]

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