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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi. Thanks for this but this solution seems to just sort the days count which I am OK with, its the months
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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