|
| 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, 03:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
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.
|
|
#3
June 13th, 2009, 04:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
YEAR MONTH DAYS COUNTER
Hi,
The undocumented DATEDIF function has the following form: DATEDIF(StartDate,EndDate,Unit) Where Units are on the left in the table below and their results on the right. Note that the Unit must be quoted - "y" for example. y Whole years between two dates m Whole months between two dates d Whole days between two dates md Number of days between two dates ignoring months ym Number of months between two dates ignoring years yd Number of days between two dates ignoring years =DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in cell A1 and A2 For reasons unknown Microsoft only choose to document this function in Excel version 2000. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
