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

=IF(B1="",DATEDIF(A1,TODAY(),"y"),DATEDIF(A1,B1,"y "))


"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

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

At http://www.cpearson.com/excel/datedif.htm Chip reveals all about the
undocumented function
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Colin Hayes" wrote in message
...
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

In article , Bernard Liengme
writes
At http://www.cpearson.com/excel/datedif.htm Chip reveals all about the
undocumented function
best wishes

HI

OK thanks - I got as far as this -

In C1 I'm putting ;

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

This gives the time in years , months and days between the dates in A1
and B1. So , for my application this calculates in C1 the length of time
a person was in the scheme. They joined on the date in A1 and left the
scheme on the date in B1. It works fine.

However , I'm having trouble where there is no date in B1. This would be
for people are still currently in the scheme and therefore have no
leaving date in B1. The formula gives a #NUM error.

I need the formula to assume that if B1 is empty that the date is the
current date now. Can anyone help with this?

Thanks.

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

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

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

=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