Count the number of whole calandar months in a date range

Hi all,

Not sure if anyone can help but I am trying to write a formula to calculate the complete number of calandar months in a date range. So for example if in cell A1 I have a start date of 17/04/12 and cell B1 I have a date of 11/09/12 I want cell C1 to give the answer of 4 as there are 4 complete calandar months within the date range?

Not sure if this is possible but would appreciate any help anyone could offer.

Thanks

Rob

You can try the formula

Formula:

Please Login or Register  to view this content.

The formula will not get displayed in the formula list of excel. But it works.

Thanks for the super quick response and welcome.

Unfortunately I have already tried this and it does not work for what I am looking for. For example if I have a start date of 17/04/12 and an end date of 28/09/12 the mentioned formula returns a value of 5 when the answer should be for as there are only 4 full/complete calandar months in the date range.... May, June, July and August.

Any suggestions?

Thank you so much works perfectly!

Hi Ace I seem to have encountered a small problem with the formula. When the difference between the two dates is less than one month or exactly one month i.e. 01/06/12 - 30/06/12 then it returns an error?

It might help if I try to explain fully what I am trying to do...

I have an annual salary figure that I am trying to apportion based on a start date and end date. The way our payroll works is to take the full annual salary (say £60k) and divide it by 12 (£5k) to work out the months salary regardless of the number of days in the month. So if you start working on 01/04/12 and finish on 15/06/12 then your salary will be £10k (2 x £5k) for the two complete months (April and May) and £2.5k for the 15 days in June because where a month is not a complete month the monthly salary (£5k) is divided by the total number of days in the relevant month (June) and multiplied by the number of days worked.

This is the formula I am currently working with but as I said above when the start date and end date are at opposite ends of the same month it returns an error i.e. 01/04/12 - 30/04/12:

=((O12/12)*IF(MONTH(F12)=MONTH(G12),0,DATEDIF(EOMONTH(F12,0)+1,EOMONTH(G12,0),"M")+IF(DAY(F12)=1,1,0)+IF(DAY(G12)=DAY(EOMONTH(G12,0)),1,0)))+(((O12/12)/DAY(EOMONTH(F12,0)))*IF(DAY(F12)=1,0,DAY(EOMONTH(F12,0))+1-DAY(F12)))+(((O12/12)/DAY(EOMONTH(G12,0)))*IF(G12=EOMONTH(G12,0),0,DAY(G12)))

O12 = Annual salary
F12 = Start Date
G12 = End Date

Thanks in advance for any help you could offer :-)

Try if this helps! Little lengthy though..

Formula:

Please Login or Register  to view this content.

Thanks for the help Sindhus but unfortunately it does not quite work. It falls down when there are odd days in the start month, end month or both. For example if the start date was 17/04/12, the end date 06/08/12 and the annual salary £40,506 then it should be calculated as follows:

- For the complete whole months within the date range i.e. May, June and July the annual salary should be divided by 12 (total number of months) and multiplied by the number of whole/complete months... ((£40,506/12)*3) = £10,126.50
- For the odd days in the start month i.e. April the monthly salary (£40,506/12) should be divided by the number of days in April (30) and multiplied by the number of days worked (14).... (((£40,506/12)/30)*14) = £1,575.23
- For the odd days in the end month it should be exactly the same as for the odd days in the start month... (((£40,506/12)/31)*26) = £2,831.06.
- The total apportioned salary cost is therefore: £10,126.50 + £1,575.23 + £2,831.06 = £14,532.79.

I do not understand the odd days in the end month portion. Can you explain it again? How the 26 days appear in the formula?

So, for the odd days in the start month/end month, you want to take the exact no. of days in the month rite?

Hi Sindhus... the start month and end month are calculated in the same way... the annual salary is divided by 12 to work out the monthly salary and then divided by the number of days in the month and multiplied by the number of days worked. So for example if an employee started on 20/04/12 then the salary cost for April would be the monthly salary divided by 30 as there are 30 days in April and then multiplied by 11 as they would have worked 11 days in the month.

Ok i modified it now. Check this.

Formula:

Please Login or Register  to view this content.

Sorry Singhus but this does not work. If I have a start date of 01/04/12 and an end date of 30/04/12 the result is equal to 2 months salary costs when it should only be one. Also it does not work when calculating the odd days in the end month but does work in calculating the days in the start month.

Oops! missed a condition given in the first formula. Try this.

Formula:

Please Login or Register  to view this content.

I addressed the single month scenario in this formula. What is the odd days in the end month part? Can you give an example?

Sorry Sindhus but it does not work. Specifically it did not work when I tried the following scenarios:

-If the start date and end date where both in the same month but not a complete month i.e. 06/04/12 - 25/04/12
-If the start date and end date where in different months but again there was not a complete calendar month i.e. 06/04/12 - 25/05/12
-If the start date was a whole month and the end date was not i.e. 01/04/12 - 17/05/12
-If the start date was not a complete month and the end date was i.e. 15/05/12 - 30/06/12

Just to clarify here is an example:

The annual salary is £120k
The start date is 15/05/12
The end date is 17/02/13

The answer should be £91,555.30

This is calculated in 3 sections as follows:

- There are 8 whole calendar months between the start and end date (June, July, August, September, October, November, December and January) so the annual salary is divided by 12 and multiplied by 8 = £80,000.00
- The start date has 17 working days 15/05/12 - 31/05/12 so the annual salary is divided by 12 then divided by 31 and multiplied by 17 = £5,483.87
- The end date also has 17 working days 01/02/13 - 17/02/13 so the annual salary is divided by 12 then divided by 28 and multiplied by 17 = £6,071.43
- £80,000.00 + £5,483.87 + £6,071.43 = £91,555.30

Hope that helps explain things :-)

Oh i thought you always want to divide by 30. Ok try this formula.

Formula:

Please Login or Register  to view this content.

Still not working I'm afraid Sindhus. The single month scenario is not working and neither is a scenario where the start month is odd days and the end month is a complete month. It should only divide by 30 when calculating the odd days for a month that is not complete and there are 30 days in that month. If there are 31 days in the month it should divide by 31 and if there are 28 then it should divide by 28.

Can you give specific examples with expected output, as you have given before?

Sure no problem, if we work with an annual salary cost for £120,000 the following scenarios could all occur...

1 whole month - So the start date would be the beginning of a month and then end date would be the end of the same month for example 01/04/12 - 30/04/12 this would be calculated by taking the annual salary of £120,000 dividing it by 12 to calculate the monthly salary of £10,000. In this scenario it does not matter how many days in the month as the employee would still receive the annual salary divided by 12.

Odd days in the same month - This is where the start date and the end date are in the same month but it is not a complete month i.e. the start date is not the 1st of the month and the end date is not last day of the month. This would be calculated by dividing the annual salary by 12 as above to give £10,000 then divide the monthly salary by the number of days in the month in question and multuplying it by the number of days worked. So if the start date was 04/05/12 and the end date was 20/05/12 the monthly salary of £10,000 would be divided by 31 as there are 31 days in May and multiplied by 17 as there are 17 days between the two dates. This should give an answer of £5,483.87.

Odd days in different months but not a complete month - This is where the start date and end date are in adjacent months but neither the start month or the end month is a complete calendar month. So for example if we have a start date of 15/04/12 and an end date of 17/05/12 the salary cost would be calculated as follows. For the start month the monthly salary of £10,000 would be divided by 30 as there are 30 days in April and multiplied by 16 as there are 16 days worked in April giving an answer of £5,333.33. The May part of the calculation would work exactly the same, the monthly salary of £10,000 would be divided by 31 as there are 31 days in May and multiplied by 17 as there are 17 days worked in May giving an answer of £5,483.87. So the the total of the two is then £5,333.33 + £5,483.87 = £10,817.20.

Several complete calendar months - This is where all of the months between the start and end date are complete calendar months. For example if the start date was 01/04/12 and the end date was 30/09/12. This would be calculated by taking the annual salary dividing it by 12 and multiplying it by the number of complete months which in this case is 6 (April, May, June, July, August, September). This would give an answer of £60,000.00.

The start month is a complete calendar month and the end month is not - This is where the start month is a complete calendar month i.e. is the 1st of the month and the end date is not a complete calendar month. For example the start date is 01/04/12 and the end date is 15/09/12. This would be calculated by taking the annual salary dividing it by 12 to give £10,000 then multiplying this by 5 as that is the number of complete calendar months between the two dates (April, May, June, July, August) this gives an answer of £50,000.00. Then for September (the end month) the monthly salary of £10,000.00 is divided by 30 as there are 30 days in September and multiplied by 15 as there are 15 days worked in September. This gives an answer of £5,000.00. Together they give a total answer of £55,000.00.

The start month is not a complete calendar month but the end month is - This is where the start month is not a complete calendar month i.e. the start date is not the 1st of the month and the end date is i.e then end date is the 28th, 30th or 31st depending on how many days are in the month. For example if the start date was 15/04/12 and the end date 30/09/12. This would be calculated by taking the monthly salary of £10,000 then dividing it by 30 as there are 30 days in April and multiplying it by 16 as there are 16 days between 15/04/12 and 30/04/12. This gives an answer of £5,333.33. Then we would take the monthly salary of £10,000 and multiply it by 5 as there are 5 complete calander months between the start and end date (May, June, July, August, September). This would give an answer of £50,000. Together the total answer would be £55,333.33.

The start month is not a complete calendar month neither is the end month but there are several complete calander months inbetween - This needs to be calculated in 3 sections. If the start month is 15/04/12 and then end month is 15/09/12 it would be calculated as follows. The monthly salary of £10,000.00 would be divided by 30 as there are 30 days in April and multiplied by 16 as there are 16 days between 15/04/12 and 30/04/12. This gives an answer of £5,333.33. The monthly salary of £10,000.00 would then need to be multiplied by 4 as there are 4 complete calendar months between the start and end date (May, June, July, August). This gives an answer of £40,000.00. For the end month the monthly salary of £10,000.00 would be divided by 30 as there are 30 days in September and multiplied by 15 as there are 15 days between 01/09/12 - 15/09/12. This would give an answer of £5,000.00. So the total answer would be £5,333.33 + £40,000.00 + £5,000.00 = £50,333.33.

Hope this all makes things clearer and thanks again for all the help.

Cool examples! All your scenarios is working with this formula. Try it out.

Formula:

Please Login or Register  to view this content.

Sindhus it works perfectly! Thank you very much for your patience and your time much appreciated!

Thank you If it is resolved, please make this post as solved.