YEAR MONTH DAYS COUNTER

[ad2ad79]

Hi,
I have a sheet in which I want to count the year then month and then
days for employee. i.e

Name Date of joining Date of leaving Absent Total
JOHN 23-08-2001 11-11-2008 15 Years-Months-Days


I want that Function or formula count first Rounded Year from
23-08-2001 23-08-2008 then count Month and then days. and then minus
Absent days and final result. Pls help me.

Best Regards.

 

[Rich/rerat]

ad2ad79,
Use DateDif Function:
A1: Start Date (format column "dd-mm-yyyy")
B1: End Date (format column "dd-mm-yyyy")
C1: Absent Days (format column "General")
D1: Difference (format column "General")

In Cell D2, place the following formula:

=IF($A2="","",DATEDIF(A2,B2,"y")&" years "&DATEDIF(A2,B2,"ym")&" months
"&DATEDIF(A2,B2,"md")-C2
&" days")"

Then drag down through column "D" the above formula, as needed.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Hi,
I have a sheet in which I want to count the year then month and then
days for employee. i.e

Name Date of joining Date of leaving Absent Total
JOHN 23-08-2001 11-11-2008 15 Years-Months-Days


I want that Function or formula count first Rounded Year from
23-08-2001 23-08-2008 then count Month and then days. and then minus
Absent days and final result. Pls help me.

Best Regards.

 

[Rich/rerat]

Barry,
Thanks for the correction, I see now where I messed up.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>



Rich, that might work for this example but in some circumstances you'll
get the wrong results, e.g. negative number for days, you need to
incorporate -D2 into DATEDIF, see my response 'here'
(http://tinyurl.com/m3huwa)

 

[JLatham]

As posted in the other copy of your question:

This formula assumes working with values on row 2, where
B2 = joined date
C2 = left date
D3 = days absent

=DATEDIF(B2-D2,C2,"y") & " Yrs, " & DATEDIF(B2-D2,C2,"ym") & " months " &
DATEDIF(B2-D2,C2,"md") & " days"

Adapted from Chip Pearson's "calculating age" solution at:
http://www.cpearson.com/excel/datedif.aspx

 

[JLatham]

You're probably right about "when" to subtract the absent days. I didn't
think about it properly. Good catch, thanks.

 

[ad2ad79]

Barry,
Thanks for the correction, I see now where I messed up.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News)    <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>



Rich, that might work for this example but in some circumstances you'll
get the wrong results, e.g. negative number for days, you need to
incorporate -D2 into DATEDIF, see my response 'here'
(http://tinyurl.com/m3huwa)

Thanks Barry ... That's what i needed thanks a lot.... so kind of u...
Thanks once again..

 

[Don Guillett]

OP sent file to me and I revised this to be now.
=IF(D16<1,"",IF(DATEDIF(C16,D16-E16,"y")<1, "",
DATEDIF(C16,D16-E16,"y") & " Yrs, ") & DATEDIF(C16,D16-E16,"ym") & " months
" &DATEDIF(C16,D16-E16,"md") & " days")