Calculating precise number of months between two specific dates

[Adetro]

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]

How do you get those values that start with 53?

 

[Adetro]

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]

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

 

[Dave Patton]

You will probably calculate the first and last month separately.
The YearFrac shows that the calculation is reasonable.

Date and Time 2021.xlsm
	A	B	C	D	E	F	G	H
1				Datedif m		Salary	10,000.00	120,000.00
2	18-Feb-21	25-Jul-21	5.1993088	4			51,993.09	51,945.21
3	19-Feb-21	15-Sep-21	5.8571429	6			68,571.43	68,712.33
4	20-Feb-21	15-Sep-21	5.8214286	6			68,214.29	68,383.56
5	21-Feb-21	27-Nov-21	9.1857143	8			91,857.14	92,054.79
6	22-Feb-21	28-Nov-21	9.1833333	9			101,833.33	92,054.79
7	23-Feb-21	29-Nov-21	9.1809524	9			101,809.52	92,054.79
8	24-Feb-21	3-Sep-25	53.278571	54			542,785.71	541,967.21
9	25-Feb-21	3-Sep-25	53.242857	54			542,428.57	541,639.34
10	26-Feb-21	4-Sep-25	53.240476	54			542,404.76	541,639.34
11	27-Feb-21	25-Feb-25	46.964286	47			479,642.86	478,688.52
12	28-Feb-21	17-Jan-22	9.5841014	10			105,841.01	106,520.55
13	1-Jan-21	31-Dec-21	12	11			120,000.00	120,000.00
14	1-Jan-20	31-Dec-20	12	11			120,000.00	120,000.00
15	1-Jul-21	31-Dec-21		5			60,000.00	60,493.15
16							2,997,381.72	2,976,153.60
5b

Cell Formulas
Range	Formula
G1	G1	=H1/12
G2,G11:G15	G2	=(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:H15	H2	=(B2-A2+1)/(365+SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"ddmmm")="29Feb")))*$H$1
G3:G10	G3	=(EOMONTH(A3,0)-A3+1)/DAY(EOMONTH(A3,0))*$G$1+D3*$G$1+(DAY(B3)/DAY(EOMONTH(B3,0)))*$G$1
G16:H16	G16	=SUM(G2:G15)
D2:D15	D2	=DATEDIF(EOMONTH(A2,0),B2,"m")

 

[Dave Patton]

as above with Helper Columns

Date and Time 2021.xlsm
	A	B	C	D	E	F	G	H	I	J
18	Start	EOM Start	End	EOM End	Datedif m	Leap Year	YearFrac	10,000.00	120,000.00
19	18-Feb-21	28-Feb-21	25-Jul-21	31-Jul-21	4	0	0.433	51,993.09	51,945.21	51,945.21
20	19-Feb-21	28-Feb-21	15-Sep-21	30-Sep-21	6	0	0.573	68,571.43	68,712.33	68,712.33
21	20-Feb-21	28-Feb-21	15-Sep-21	30-Sep-21	6	0	0.570	68,214.29	68,383.56	68,383.56
22	21-Feb-21	28-Feb-21	27-Nov-21	30-Nov-21	8	0	0.767	91,857.14	92,054.79	92,054.79
23	22-Feb-21	28-Feb-21	28-Nov-21	30-Nov-21	9	0	0.767	101,833.33	92,054.79	92,054.79
24	23-Feb-21	28-Feb-21	29-Nov-21	30-Nov-21	9	0	0.767	101,809.52	92,054.79	92,054.79
25	24-Feb-21	28-Feb-21	3-Sep-25	30-Sep-25	54	1	4.526	542,785.71	541,967.21	543,154.44
26	25-Feb-21	28-Feb-21	3-Sep-25	30-Sep-25	54	1	4.524	542,428.57	541,639.34	542,825.85
27	26-Feb-21	28-Feb-21	4-Sep-25	30-Sep-25	54	1	4.524	542,404.76	541,639.34	542,825.85
28	27-Feb-21	28-Feb-21	25-Feb-25	28-Feb-25	47	1	3.998	479,642.86	478,688.52	479,737.13
29	28-Feb-21	28-Feb-21	17-Jan-22	31-Jan-22	10	0	0.888	105,841.01	106,520.55	106,520.55
30	1-Jan-21	31-Jan-21	31-Dec-21	31-Dec-21	11	0	1.000	120,000.00	120,000.00	120,000.00
31	1-Jan-20	31-Jan-20	31-Dec-20	31-Dec-20	11	1	1.000	120,000.00	120,000.00	120,000.00
32	1-Jul-21	31-Jul-21	31-Dec-21	31-Dec-21	5	0	0.504	60,000.00	60,493.15	60,493.15
33								2,997,381.72	2,976,153.60	2,980,762.44
5b

Cell Formulas
Range	Formula
H18	H18	=I18/12
B19:B32,D19:D32	D19	=EOMONTH(C19,0)
E19:E32	E19	=DATEDIF(B19,C19,"m")
F19:F32	F19	=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A19&":"&C19)),"ddmmm")="29Feb"))
G19:G32	G19	=YEARFRAC(A19-1,C19,1)
H19:H32	H19	=(B19-A19+1)/DAY(B19)*$H$18+E19*$H$18+(D19<>C19)*(DAY(C19)/DAY(D19))*$H$18
I19:I32	I19	=(C19-A19+1)/(365+F19)*$I$18
J19:J32	J19	=G19*$I$18
H33:J33	H33	=SUM(H19:H32)

 

[Sulprobil]

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

 

[Adetro]

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]

What is the difference between true and false formula?

 

[Dave Patton]

Version with MS 365; it is a bit more concise.

Date and Time 2021.xlsm
	A	B	C	D
1	Monthly salary		10,000.00	Factor
2	18-Feb-21	25-Jul-21	51,993.09	5.199309
3	19-Feb-21	15-Sep-21	68,571.43	6.857143
4	20-Feb-21	15-Sep-21	68,214.29
5	21-Feb-21	27-Nov-21	91,857.14
6	22-Feb-21	28-Nov-21	91,833.33
7	23-Feb-21	29-Nov-21	91,809.52
8	24-Feb-21	3-Sep-25	542,785.71
9	25-Feb-21	3-Sep-25	542,428.57
10	26-Feb-21	4-Sep-25	542,404.76
11	27-Feb-21	25-Feb-25	479,642.86
12	28-Feb-21	17-Jan-22	105,841.01
13	1-Jan-21	31-Dec-21	120,000.00
14	1-Jan-20	31-Dec-20	120,000.00
15	1-Jul-21	31-Dec-21	60,000.00
16			2,977,381.72
5b

Cell Formulas
Range	Formula
D2:D3	D2	=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:C15	C2	=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)
C16	C16	=SUM(C2:C15)