Simple Date Calculation - Length of Service

stacyen

New Member
Joined
Aug 21, 2002
Messages
2
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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!
 
Upvote 0
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)
 
Upvote 0
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

????
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top