# Add or subtract durations in years, months, days format

#### Tlsoi

##### New Member
Hello everyone,

I am trying to add or subtract durations in years, months, days format; but i could not figure it out how to do it.

For example, Column A, B and C refers to years, months and days respectively.

A1 = 4 (years), B1 = 7 (months), C1 = 19 (days)
A2 = 1 (years), B2 = 11 (months), C2 = 20 (days)

I want to add or subtract these two rows and show it in row 3.
If i add them up, the result will be 5 years, 18 months, 39 days. However, i want it to shift the days bigger than 30 and the months bigger than 12 to aside and automatically calculate the result as 6 years, 7 months and 9 days.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### jasonb75

##### Well-known Member
Something like this perhaps?
Book1
ABC
1YearsMonthsDays
24719
311120
4
5679
Sheet1
Cell Formulas
RangeFormula
A5A5=SUM(A2:A3,INT(SUM(B2:B3,INT(SUM(C2:C3)/30))/12))
B5B5=MOD(SUM(B2:B3,INT(SUM(C2:C3)/30)),12)
C5C5=MOD(SUM(C2:C3),30)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### jtakw

##### Well-known Member
Hi,

Try:

Book3.xlsx
ABC
14719
211120
3679
Sheet854
Cell Formulas
RangeFormula
A3A3=A1+A2+INT((B1+B2)/12)
B3B3=MOD(B1+B2,12)+INT((C1+C2)/30)
C3C3=MOD(C1+C2,30)

#### Tlsoi

##### New Member
Dear jasonb75 and jtakw, thank you so much. I am grateful for your help.

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Replies
1
Views
87
Replies
4
Views
495
Replies
3
Views
66
Replies
4
Views
423
Replies
2
Views
279

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,578
Messages
5,765,200
Members
425,267
Latest member
bishopc22

### 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.

### Which adblocker are you using?

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

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