Total Number of Years, Months, and Days

[gymwrecker]

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?

 

[jtakw]

Hi,

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

What constitute a year, 360 days, 364 days, 365 days?

 

[gymwrecker]

JTAKW, Thank you..!

30 days for a 1-Month, and 365 days for 1-Year

 

[jtakw]

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?

 

[gymwrecker]

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!!!!

 

[jtakw]

Ok, here's one way:

Book1
	C	D	E	F
1		YRS	MO	DAYS
2		2	3	15
3		4	3	22
4		6	33	20
5		2	10	2
6		1	3	30
7		1	10	28
8
9	TOTAL	16	62	117
10
11	SOLUTION	21	5	27
Sheet153

Cell Formulas
Range	Formula
D11	=INT((INT(F9/30)+E9)/12)+D9
E11	=MOD((INT(F9/30)+E9)/12,1)*12
F11	=MOD(F9/30,1)*30

 

[gymwrecker]

JTAKW, exactly what I needed. Thank you so much for the help and time! Have a great night!!!

 

[jtakw]

You're welcome.