Calculating months if all days count (DATEDIF)

[cwilliams96]

Hi

I have an issue where I want to count complete months where all days count in the month.

For example if you look at the difference between 2 dates, so 01/01/19 minus 01/01/19 we get 0, but say I want to say someone attended a course on that day, they in fact completed 1 day. So I want the formula to say 1. So I just add 1 in the formula.

Using date dif function on months, if I use say 01/01/19 to 31/1/19 the formula returns 0. In practical terms if I say some attended on these dates, they have completed 1 month, not 'no months'

How can I get a formula to give me the answer of 1 month completed in this scenario...

As another example. so if it was across 01/01/19 - 28/02/19 I would get 2 months, where the formula now gives me 1 month.

Thanks for all your help
Chris

 

[Dr. Demento]

I don’t know this formula very well, but it seems that someone else had a similar problem with inclusive dates. Perhaps this will help.

https://www.mrexcel.com/forum/excel...wsxp-how-count-inclusive-start-end-dates.html

 

[cwilliams96]

Hi. Thanks for this but this solution seems to just sort the days count which I am OK with, its the months

 

[AhoyNC]

Maybe just subtract the months and add 1.

Excel Workbook
	A	B	C
1	Start Date	End Date	# Months
2	1/2/2019	1/30/2019	1
3	2/2/2019	4/5/2019	3
4	2/2/2019	3/1/2019	2
5	5/6/2019	5/20/2019	1
Sheet

 

[AhoyNC]

The formula I gave above would only work if months are in the same year.
Instead maybe:

Excel Workbook
	A	B	C
1	Start Date	End Date	Months
2	1/1/2019	1/3/2019	1
3	1/8/2019	2/1/2019	2
4	12/6/2018	2/1/2019	3
5	12/4/2018	3/3/2019	4
6	2/2/2019	6/4/2019	5
7	10/5/2017	2/6/2019	17
Sheet