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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,401
Office Version
365
Platform
Windows
Maybe just subtract the months and add 1.
Excel Workbook
ABC
1Start DateEnd Date# Months
21/2/20191/30/20191
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,401
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/20191
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
 

Forum statistics

Threads
1,089,169
Messages
5,406,583
Members
403,097
Latest member
danielcarrington

This Week's Hot Topics

Top