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.

 

[barry houdini]

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)

 

[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

 

[barry houdini]

But shouldn't the absent days be subtracted from the total duration? By
subtracting from the start date you are effectively adding those days.
I'd suggest that you either need to add D2 to B2 or subtract D2 from C2,
either would have the same affect.

I posted this solution on another version of this thread (as alluded to
above)

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

 

[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")