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,412
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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,412
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,627
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,627
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
147
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,051
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,627
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,127,334
Messages
5,624,071
Members
416,010
Latest member
NJT

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