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


"Colin Hayes" wrote:

In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Colin

Combine TM's formula using this format this to suit the IF clause to cover a
blank B1

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


Gord Dibben MS Excel MVP

Hi Gord

Do you mean like this ?

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

This gives errors - but I'm sure my syntax is not far off. (!). i'm not
sure how to join the two halves together.

I did actually send a post just before receiving yours , so forgive any
repetition.

Best Wishes


Colin





On Sat, 12 May 2007 19:23:17 +0100, Colin Hayes
wrote:

In article ,
Teethless mama writes
=IF(B1="",DATEDIF(A1,TODAY(),"y"),DATEDIF(A1,B1 ,"y"))

Hi

OK thanks.

I tried it out - could it be extended to give results in

n 'Years' n 'Months' n 'Days' format ?

If you could help that would great.

Also , if A1 or B1 is blank then the formula cell C1 needs to remain
blank to if possible , please.


Grateful for your assistance.

Best Wishes


"Colin Hayes" wrote:


Hi

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