Months between two dates - allowing for half months

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
Hello,

How do I calculate the number of months between two dates - allowing for half months. I am building a holiday entitlement calculator. The number of days will reduce if someone leaves during the year and depending on when exactly they leave they may be entitled to half a months worth of leave. Thank you.

kind regards
Kaps
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows
It really depends on how you define "half-months". Does that mean 15 days? Or do you have to be there on a certain day of the month?
This needs to be clearly defined in order for us to come up with a good solution that will work according to your needs.

To this end, it would also be helpful to see a bunch of examples of dates, and what your expected result is for each one.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
It really depends on how you define "half-months". Does that mean 15 days? Or do you have to be there on a certain day of the month?
This needs to be clearly defined in order for us to come up with a good solution that will work according to your needs.

To this end, it would also be helpful to see a bunch of examples of dates, and what your expected result is for each one.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
Hello Joe,

15 days would be sufficient. Thank you.

kind regards
Kaps
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It sounds like you just need to subtract the two dates, divide by 15, and take the integer portion of that.
So, if the start date was in cell A1, and the end date was in cell A2, the formula would look like:
Excel Formula:
=INT((A2-A1)/15)
 
Solution

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
It sounds like you just need to subtract the two dates, divide by 15, and take the integer portion of that.
So, if the start date was in cell A1, and the end date was in cell A2, the formula would look like:
Excel Formula:
=INT((A2-A1)/15)
thanks - that looks good to me.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,388
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,137,154
Messages
5,679,911
Members
419,862
Latest member
Bluewings666

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
Top