year of service

[sachin483]

i am using year of service formula as below but if the date is blank it is catching 119 instead of zero i want to do zero service

=DATEDIF(A2,TODAY(),"y")&" Years "&DATEDIF(A2,TODAY(),"ym")&" Months "&DATEDIF(A2,TODAY(),"md")&" Days"

[TABLE="width: 431"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]21-Jun-97[/TD]
[TD][/TD]
[TD]21 Years 7 Months 24 Days[/TD]
[/TR]
[TR]
[TD]26-Feb-97[/TD]
[TD][/TD]
[TD]21 Years 11 Months 19 Days[/TD]
[/TR]
[TR]
[TD]09-Jul-01[/TD]
[TD][/TD]
[TD]17 Years 7 Months 5 Days[/TD]
[/TR]
[TR]
[TD]12-Mar-05[/TD]
[TD][/TD]
[TD]13 Years 11 Months 2 Days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]119 Years 1 Months 14 Days[/TD]
[/TR]
</tbody>[/TABLE]

 

[alpadem]

i am using year of service formula as below but if the date is blank it is catching 119 instead of zero i want to do zero service

=DATEDIF(A2,TODAY(),"y")&" Years "&DATEDIF(A2,TODAY(),"ym")&" Months "&DATEDIF(A2,TODAY(),"md")&" Days"

[TABLE="width: 431"]
<tbody>[TR]
[TD]21-Jun-97[/TD]
[TD][/TD]
[TD]21 Years 7 Months 24 Days[/TD]
[/TR]
[TR]
[TD]26-Feb-97[/TD]
[TD][/TD]
[TD]21 Years 11 Months 19 Days[/TD]
[/TR]
[TR]
[TD]09-Jul-01[/TD]
[TD][/TD]
[TD]17 Years 7 Months 5 Days[/TD]
[/TR]
[TR]
[TD]12-Mar-05[/TD]
[TD][/TD]
[TD]13 Years 11 Months 2 Days[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]119 Years 1 Months 14 Days[/TD]
[/TR]
</tbody>[/TABLE]

=IF(A2<>"",DATEDIF(A2,TODAY(),"y")&" Years "&DATEDIF(A2,TODAY(),"ym")&" Months "&DATEDIF(A2,TODAY(),"md")&" Days",DATEDIF(TODAY(),TODAY(),"y")&" Years "&DATEDIF(TODAY(),TODAY(),"ym")&" Months "&DATEDIF(TODAY(),TODAY(),"md")&" Days"

 

[sachin483]

thanks a lot working fine