Total Number of Years, Months, and Days

gymwrecker

Active Member
Joined
Apr 24, 2002
Messages
396
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Team,

I'm working with "Net Periods of Service" and need a formula that converts days into months and years, and/or months into years. For instance, this is what I have: Note: Using MS Excel 2016 (MAC Version)

Net Active this period
YRS MO DAYS
DD214 Separation Date 2 3 15
DD214 Separation Date 4 3 22
DD214 Separation Date 6 33 20
DD214 Separation Date 2 10 2
DD214 Separation Date 1 3 30
DD214 Separation Date 1 10 28

TOTAL 16YRS 62MO 117DAYS

SOLUTION: NET TOTAL 21YRS 4MO 9DAYS (Need a formula to obtain this using the above totals)

How can I convert the 177 days into months, and the 62 months into years?
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi,

You'll need to define what constitutes a month, 30 days, 31 days?

What constitute a year, 360 days, 364 days, 365 days?
 
Upvote 0
JTAKW, Thank you..!

30 days for a 1-Month, and 365 days for 1-Year
 
Upvote 0
The sample you show in OP, is your data in separate columns?
The result, do you want them in separate columns? With the "words" Year, Month, Day?
 
Upvote 0
Yes, there are three separate columns; one for YEAR, one for MONTHS, and one for DAYS. I don't need the words YEAR/MONTH/DAYS since the columns are identified at the top... Thanks again!!!!
 
Upvote 0
Ok, here's one way:


Book1
CDEF
1YRSMODAYS
22315
34322
463320
52102
61330
711028
8
9TOTAL1662117
10
11SOLUTION21527
Sheet153
Cell Formulas
RangeFormula
D11=INT((INT(F9/30)+E9)/12)+D9
E11=MOD((INT(F9/30)+E9)/12,1)*12
F11=MOD(F9/30,1)*30
 
Upvote 0
JTAKW, exactly what I needed. Thank you so much for the help and time! Have a great night!!!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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