|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 13th, 2009, 10:27 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YEAR MONTH DAYS COUNTER
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. 
|
|
#2
June 13th, 2009, 02:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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.
|
|
#3
June 13th, 2009, 02:35 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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
|
|
#4
June 13th, 2009, 03:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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
|
|
#5
June 13th, 2009, 03:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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.
|
|
#6
June 13th, 2009, 04:22 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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
|
|
#7
June 13th, 2009, 11:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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
|
|
#8
June 14th, 2009, 09:51 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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..
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
