Count months between date values using YEAR as criteria

Shallabee

New Member
Joined
Jan 31, 2018
Messages
4
Hi,


I need to count number of months that occur over a specific date range that fall in a specified year and the date ranges could be 1.1.2016 to 1.10.2017 and i'm only interested in months that occur in 2017, also the date formats used in the raw data spreadsheet source are typically in the format yyyy mm (2016 10), and typically get a number of entries that have a start of say 2017 10 and end date 2017 10 which should equal 1 month. example below.


Start Date Endate Duration 2017 MTH COUNT
2015 10 2018 09 36 12
2017 07 2017 07 1 1
2017 01 2017 12 12 12
2017 07 2017 09 3 3
2017 04 2017 04 1 1
2017 02 2017 12 11 11
2017 09 2017 12 4 4
2017 06 2020 05 36 12
2017 12 2017 12 1 1
2015 05 2017 04 24 4
2017 03 2017 03 1 1
2015 04 2017 04 25 4
2017 11 2017 11 1 1
2017 11 2017 11 1 1
2017 08 2018 07 12 5
2017 10 2017 10 1 1
2017 09 2017 09 1 1
2017 07 2018 06 12 6


There is another part to this calculation where the number of months are multiplied by a unit value, but have managed to crack that one... any help would be appreciated.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
=DATEDIF(MAX(A2,DATE(2017,1,1)),MIN(B2,DATE(2017,12,31)),"ym")+1
 
Upvote 0
04/03/20162016 03
07/04/20162016 04
11/05/20162016 05
14/06/20162016 06
18/07/20162016 07
21/08/20162016 08
24/09/20162016 09
28/10/20162016 10say you want march to august in 2017
01/12/20162016 12
04/01/20172017 01start date01/03/2017
07/02/20172017 02end date31/08/2017
13/03/20172017 03
16/04/20172017 04
20/05/20172017 05the formula works with col B
23/06/20172017 06
27/07/20172017 07
30/08/20172017 08count
03/10/20172017 10
06/11/20172017 116
10/12/20172017 12
13/01/20182018 01=SUMPRODUCT((B1:B22>=$I$10)*(B1:B22<=$I$11)*1)
16/02/20182018 02

<colgroup><col span="2"><col span="6"><col><col span="7"></colgroup><tbody>
</tbody>
 
Upvote 0
=DATEDIF(MAX(A2,DATE(2017,1,1)),MIN(B2,DATE(2017,12,31)),"ym")+1

Thanks, but this formula seems to always return a value of 12 regardless of the cell start and end dates.

Start Date End Date Count of Months

2017 01 2017 01 1
2017 07 2018 06 6
2015 10 2018 09 12

Numbers should correspond to count of months column.
 
Upvote 0
It works for me. Do you have dates in A2 and B2 ?
 
Upvote 0
I changed the cell references to the start and end month cells on my source sheet and still get a constant 12, have tried in both mac and pc versions and same result?
 
Upvote 0
How are the cells formatted ? As dates or as text?
The formula will only work if the cells in columns A and B are dates.
If they are text (or blank), the formula will return 12.
 
Upvote 0
Thanks, did eventually get this to work, it looks like it was a formatting issue as the yyyy mm format would not work properly, but when i formatted the date values to dd/mm/yyyy it all worked... Excellent...
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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