In article ,
Teethless mama writes
=IF(A1="","",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"))



Hi TM

Ok that's perfect! Thanks a lot for helping me out on this.

It's working seamlessly now.

^_^

Best Wishes


Colin



"Colin Hayes" wrote:

In article ,
Teethless mama writes
=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")

Hi TM

OK Thanks for that - it's working fine now. ^_^

Still get readings in C of 107 years, 4 months, 12 days when A is empty
though. I'd have though C would be empty if there was nothing in A or B.
(?)

(Some of the people on my list aren't in the scheme , so have a blank in
the Date Joined column A ....)

Thanks again




"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