Time difference in Months, but represented for each Year separately

tomekm

New Member
Joined
Mar 31, 2017
Messages
9
Given a start date and end date, I'm trying to calculate the time diff. in number of Months, BUT representing the count of the Months per each year. For example:

Start Date: 01/25/2020
End Date: 06/28/2023


Occurrences in 2020 only: formula I have: timediff (in months) between Start Date and 12/31/2020
Occurrences in 2021 only: need formula here (I cannot hard code the value of 12)
Occurrences in 2022 only: need formula here (I cannot hard code the value of 12)
Occurrences in 2023 only: formula I have: timediff (in months) between 01/01/2023 and End Date

Thank you!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,384
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@tomekm Not sure of your existing formula and whether it rounds or whatever but maybe like below to capture the 'inbetween' 12's ?

Bridal Stuff2.xlsx
AB
1Start25/01/2020
2End28/06/2023
3
42020Your Existing formula?
5202112
6202212
72023Your Existing formula?
Sheet7
Cell Formulas
RangeFormula
B4:B7B4=IF(AND(YEAR(B$1)<A4,YEAR(B$2)>A4),12,"Your Existing formula?")


Hope that helps.
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
384
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
If you have 365, this would be nice.
MrExcelPlayground4.xlsx
ABCDE
1Yearmonths
2Start1/25/2020202012
3End6/28/2023202112
4202212
520236
Sheet4
Cell Formulas
RangeFormula
D2:D5D2=SEQUENCE((YEAR(B3)-YEAR(B2)+1))+YEAR(B2)-1
E2:E5E2=SUM((--(DATE(D2,SEQUENCE(12),1)>=DATE(YEAR($B$2),MONTH($B$2),1))*(--(DATE(D2,SEQUENCE(12),1)<=DATE(YEAR($B$3),MONTH($B$3),DAY($B$3))))))
Dynamic array formulas.


I think there are work arounds for older excel.
 

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

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