rounding dates to nearest month

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
Hi

In cell C1 I have a start date (this could be blank).
In cell D1 I have an end date (also could be blank).

I would like to work out the difference in months between the end date and the start date.

however...

If the start/end date DAY is less than 15 then I want to consider it a full month.

If the start/end date DAY is greater than or equal to 15 then I do not want to consider the month.

I have the following to work out the difference but it's big and ugly and I figure there must be a slicker method.

Code:
=IF($D3="",13-IF(DAY(C3)>15,MONTH($C3)+1,MONTH(C3)),IF(D3>15,MONTH($D3)+1,MONTH(D3))-IF(DAY(C3)>15,MONTH($C3)+1,MONTH(C3)))

Any help / suggestions appreciated :)

Regards
Jon
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hello Jon,

so if C1 is today (8th June 2007) and D1 is, say, 20th July 2007, is that 2 months? If so perhaps

=IF(D1="","",DATEDIF(C1-DAY(C1)+1,D1,"m")+(DAY(D1)>15)-(DAY(C1)>15))
 
Upvote 0
Yes you got it!!!! :biggrin:

Thanks Barry, this is alot easier to follow than my big fugly formula! :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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