Calculating precise number of months between two specific dates

Adetro

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

Many thanks in advance
Paul
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,774
Office Version
  1. 365
Platform
  1. Windows
How do you get those values that start with 53?
 

Some videos you may like

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.

Adetro

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
How do you get those values that start with 53?
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
Joined
Jun 12, 2014
Messages
55,774
Office Version
  1. 365
Platform
  1. Windows
They're more than a month out, 24th Feb 21 to 24th Mar 25 is only 49 months.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 12, 2020
Messages
148
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:

Adetro

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
What is the difference between true and false formula?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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.
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