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.

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


"ad2ad79" wrote in message
...
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, 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)


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106332

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


"barry houdini" wrote in message
...

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)


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile:
http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106332

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


"ad2ad79" wrote:

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.

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"


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106332

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

"barry houdini" wrote:


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"


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106332

On Jun 13, 6:50*pm, "Rich/rerat" wrote:
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

"barry houdini" wrote in message

...

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)

--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile:http://www.thecodecage.com/forumz/member.php?userid=72
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=106332

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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"barry houdini" wrote in message
...

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"


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile:
http://www.thecodecage.com/forumz/member.php?userid=72
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=106332