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

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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.
 
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
 
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
 
Hi

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


Best Wishes


Colin
 
Paul B said:
Colin, like this,
=IF(A1="","",DATEDIF(A1,NOW(),"y")&" years, "&DATEDIF(A1,NOW(),"ym")&"
months,"&DATEDIF(A1,NOW(),"md")&" days")
Click to expand...

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
 
Paul B said:
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"))
Click to expand...

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
 
Paul B said:
Colin, glad I could help, just so you know nobody gets paid to answer any
questions here
Click to expand...

Hi Paul

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

^_^

Colin
 
Back
Top