# Calculating precise number of months between two specific dates

##### New Member
Hello. I'd really like some help with this. I really appreciate any replies.

I need to do forecasting on salary costs for staff members. They are paid a salary which is divided equally between the months. So if their salary is £36,000, then they get paid £3,000 in February, and £3,000 in March, and £3,000 in every month of the year.

I have been trying to tweak the below formulae which I've found in a thread from 2012. Both formula does the same thing. However, currently those formulae assume that the start date is to be discounted from the answer, but I need it to be inclusive.

=(DAY(EOMONTH(A1,0))-DAY(A1))/DAY(EOMONTH(A1,0))+DATEDIF(A1,B1,"m")-1+DAY(B1)/DAY(EOMONTH(B1,0))

=DATEDIF(A2,B2,"m")+DAY(B2)/DAY(EOMONTH(B2,0))-DAY(A2)/DAY(EOMONTH(A2,0))

Thus, someone will work for us as follows:
Start date: 01/07/2021 (or 07/01/2021 in US terminology)
End date: 31/12/2021
This is exactly 6.0 months. But using the above formulae I get 5.96774 months, because it's subtracting 1st July.

What I need is the precise formula which would also include 1st July in the months counted.

But it also needs to work if someone is commencing or departing at any point in a month, not necessarily just the start/end of a month

Paul

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

##### New Member
That's a good question. They're a month out. So I don't even have a work around on the formula I'm currently using. #### Fluff

##### MrExcel MVP, Moderator
They're more than a month out, 24th Feb 21 to 24th Mar 25 is only 49 months.

#### Dave Patton

##### Well-known Member
You will probably calculate the first and last month separately.
The YearFrac shows that the calculation is reasonable.

Date and Time 2021.xlsm
ABCDEFGH
1Datedif mSalary10,000.00120,000.00
218-Feb-2125-Jul-215.1993088451,993.0951,945.21
319-Feb-2115-Sep-215.8571429668,571.4368,712.33
420-Feb-2115-Sep-215.8214286668,214.2968,383.56
521-Feb-2127-Nov-219.1857143891,857.1492,054.79
622-Feb-2128-Nov-219.18333339101,833.3392,054.79
723-Feb-2129-Nov-219.18095249101,809.5292,054.79
824-Feb-213-Sep-2553.27857154542,785.71541,967.21
925-Feb-213-Sep-2553.24285754542,428.57541,639.34
1026-Feb-214-Sep-2553.24047654542,404.76541,639.34
1127-Feb-2125-Feb-2546.96428647479,642.86478,688.52
1228-Feb-2117-Jan-229.584101410105,841.01106,520.55
131-Jan-2131-Dec-211211120,000.00120,000.00
141-Jan-2031-Dec-201211120,000.00120,000.00
151-Jul-2131-Dec-21560,000.0060,493.15
162,997,381.722,976,153.60
5b
Cell Formulas
RangeFormula
G1G1=H1/12
G2,G11:G15G2=(EOMONTH(A2,0)-A2+1)/DAY(EOMONTH(A2,0))*\$G\$1+D2*\$G\$1+(EOMONTH(B2,0)<>B2)*(DAY(B2)/DAY(EOMONTH(B2,0)))*\$G\$1
H2:H15H2=(B2-A2+1)/(365+SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"ddmmm")="29Feb")))*\$H\$1
G3:G10G3=(EOMONTH(A3,0)-A3+1)/DAY(EOMONTH(A3,0))*\$G\$1+D3*\$G\$1+(DAY(B3)/DAY(EOMONTH(B3,0)))*\$G\$1
G16:H16G16=SUM(G2:G15)
D2:D15D2=DATEDIF(EOMONTH(A2,0),B2,"m")

#### Dave Patton

##### Well-known Member

as above with Helper Columns

Cell Formulas
RangeFormula
H18H18=I18/12
B19:B32,D19:D32D19=EOMONTH(C19,0)
E19:E32E19=DATEDIF(B19,C19,"m")
F19:F32F19=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A19&":"&C19)),"ddmmm")="29Feb"))
G19:G32G19=YEARFRAC(A19-1,C19,1)
H19:H32H19=(B19-A19+1)/DAY(B19)*\$H\$18+E19*\$H\$18+(D19<>C19)*(DAY(C19)/DAY(D19))*\$H\$18
I19:I32I19=(C19-A19+1)/(365+F19)*\$I\$18
J19:J32J19=G19*\$I\$18
H33:J33H33=SUM(H19:H32)

#### Sulprobil

##### Board Regular
Excel Formula:
``=DATEDIF(A2,B2,"m")+IF(DAY(B2)>=DAY(A2),(DAY(B2)-DAY(A2))/DAY(EOMONTH(B2,0)),(DAY(EOMONTH(A2,0))-DAY(A2))/DAY(EOMONTH(A2,0))+DAY(B2)/DAY(EOMONTH(B2,0)))``

Eventually you might want to use B2+1 instead of just B2.

Last edited:

##### New Member

Hi folks

Thanks very much for all of your efforts to help me. I do really appreciate it. You've all helped me to learn more about Excel.

In the end, I was able to find the correct answer on a message board elsewhere. So I'll just leave the formula here:

 =IF(MONTH(A1)=MONTH(B1),((YEAR(B1)-YEAR(A1))*12)-12+(12-MONTH(A1))+MONTH(B1)-1+(EOMONTH(A1,0)-A1+1)/DAY(EOMONTH(A1,0))+(1-(EOMONTH(B1,0)-B1)/DAY(EOMONTH(B1,0))),((YEAR(B1)-YEAR(A1))*12)-12+(12-MONTH(A1))+MONTH(B1)-1+(EOMONTH(A1,0)-A1+1)/DAY(EOMONTH(A1,0))+(1-(EOMONTH(B1,0)-B1)/DAY(EOMONTH(B1,0))))​

#### mart37

##### Well-known Member
What is the difference between true and false formula?

#### Dave Patton

##### Well-known Member
Version with MS 365; it is a bit more concise.

Date and Time 2021.xlsm
ABCD
1Monthly salary10,000.00Factor
218-Feb-2125-Jul-2151,993.095.199309
319-Feb-2115-Sep-2168,571.436.857143
420-Feb-2115-Sep-2168,214.29
521-Feb-2127-Nov-2191,857.14
622-Feb-2128-Nov-2191,833.33
723-Feb-2129-Nov-2191,809.52
824-Feb-213-Sep-25542,785.71
925-Feb-213-Sep-25542,428.57
1026-Feb-214-Sep-25542,404.76
1127-Feb-2125-Feb-25479,642.86
1228-Feb-2117-Jan-22105,841.01
131-Jan-2131-Dec-21120,000.00
141-Jan-2031-Dec-20120,000.00
151-Jul-2131-Dec-2160,000.00
162,977,381.72
5b
Cell Formulas
RangeFormula
D2:D3D2=LET(DS,EOMONTH(A2,0),DE,EOMONTH(B2,0),((DS-A2+1)/DAY(DS)+DATEDIF(DS+1,EOMONTH(B2,-1)+1,"m")+(DE=B2)+(DE<>B2)*(DAY(B2)/DAY(DE))))
C2:C15C2=LET(DS,EOMONTH(A2,0),DE,EOMONTH(B2,0),((DS-A2+1)/DAY(DS)+DATEDIF(DS+1,EOMONTH(B2,-1)+1,"m")+(DE=B2)+(DE<>B2)*(DAY(B2)/DAY(DE)))*\$C\$1)
C16C16=SUM(C2:C15)

Last edited: Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,533
Messages
5,854,291
Members
431,636
Latest member
shabbas313 ### 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.

### Which adblocker are you using?    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

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