Add or subtract durations in years, months, days format

Tlsoi

New Member
Joined
Mar 22, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.

I really apprecatiate your help.
 

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
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,027
Office Version
  1. 2016
Platform
  1. Windows
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)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,027
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
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
Top