Results 1 to 5 of 5

Simple Date Calculation - Length of Service

This is a discussion on Simple Date Calculation - Length of Service within the Excel Questions forums, part of the Question Forums category; I have two date fields, and I want to calculate Length of Service, showing the results in years and months. ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    Stacy
    Posts
    2

    Default

    I have two date fields, and I want to calculate Length of Service, showing the results in years and months. I seem to have forgotten my beginning excel formulas.

    Thanks!

  2. #2
    New Member
    Join Date
    Aug 2002
    Location
    Stacy
    Posts
    2

    Default

    On 2002-08-22 10:12, stacyen wrote:
    I have two date fields, and I want to calculate Length of Service, showing the results in years and months. I seem to have forgotten my beginning excel formulas.

    Thanks!

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    =DATEDIF(A1,A2,"Y")&" years "&DATEDIF(A1,A2,"YM")&" months"

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,501

    Default

    On 2002-08-22 10:12, stacyen wrote:
    I have two date fields, and I want to calculate Length of Service, showing the results in years and months. I seem to have forgotten my beginning excel formulas.

    Thanks!
    =DATEDIF(A13,B13,"y")&" yrs "&DATEDIF(A13,B13,"ym")&" months"

    =DATEDIF(A1,B1,"y")&"."&DATEDIF(A1,B1,"ym")

    =YEARFRAC(A1,B1)

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Default Re: Simple Date Calculation - Length of Service

    What if I want Length of Service in Years, Months and Days? I have used a formula off here and while it seems to work the days have 12 decimal places. I don't need or want decimal places in my days. Any other formula I use seems to come up with answers like 133 days or something really off like that. I'm not sure what is going on. I am using two fields, one for the current date [which is itself a formula] and one with the Full Time date of the employee. This is the formula I used:

    =YEAR($A$3)-YEAR(N5)-(TEXT($A$3,"mmdd")< TEXT(N5,"mmdd"))&" years "&MOD(MONTH($A$3)-MONTH(N5)-(DAY($A$3)< DAY(N5)),12)&" months "&$A$3-MIN(DATE(YEAR($A$3),MONTH($A$3)-(DAY($A$3)< DAY(N5))+{1,0},DAY(N5)*{0,1}))&" days"

    This is the answer I get using the date of 1/12/12 and 7/23/07
    4 years 5 months 20.30211261574 days

    ????

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com