Calculating months if all days count (DATEDIF)

cwilliams96

Board Regular
Joined
Jul 27, 2003
Messages
186
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

cwilliams96

Board Regular
Joined
Jul 27, 2003
Messages
186
Hi. Thanks for this but this solution seems to just sort the days count which I am OK with, its the months
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,499
Office Version
365
Platform
Windows
Maybe just subtract the months and add 1.
Excel Workbook
ABC
1Start DateEnd Date# Months
21/2/20191/30/2019
1
32/2/20194/5/20193
42/2/20193/1/20192
55/6/20195/20/20191
Sheet
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,499
Office Version
365
Platform
Windows
The formula I gave above would only work if months are in the same year.
Instead maybe:
Excel Workbook
ABC
1Start DateEnd DateMonths
21/1/20191/3/2019
1
31/8/20192/1/20192
412/6/20182/1/20193
512/4/20183/3/20194
62/2/20196/4/20195
710/5/20172/6/201917
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,096,420
Messages
5,450,308
Members
405,605
Latest member
Hagman

This Week's Hot Topics

Top