Comparing two columns containing Date info

C

Colin Hayes

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
 
C

Colin Hayes

=IF(B1="",DATEDIF(A1,TODAY(),"y"),DATEDIF(A1,B1,"y"))
Click to expand...

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
 
G

Gord Dibben

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
 
C

Colin Hayes

Bernard Liengme said:
At http://www.cpearson.com/excel/datedif.htm Chip reveals all about the
undocumented function
best wishes
Click to expand...

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

Colin Hayes

Gord Dibben said:
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
Click to expand...

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
 
T

Teethless mama

=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")
 
C

Colin Hayes

=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")
Click to expand...
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
 
T

Teethless mama

=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"))
 
C

Colin Hayes

=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"))
Click to expand...

Hi TM

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

It's working seamlessly now.

^_^

Best Wishes


Colin
 
Top