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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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:

Replies
2
Views
85
Replies
2
Views
72
Replies
14
Views
217
Replies
5
Views
229
Replies
2
Views
106

1,128,092
Messages
5,628,623
Members
416,329
Latest member
phxdan79

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.

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