Date has caused DATEDIF based formula to be incorrect

[chive90]

C5 = Yearly salary figure
C12 = Salary left to be paid 23/24 FY figure

E18 = First salary payment date (30/04/23)
E19 = Last salary payment date (31/03/24)

Formula in cell C12 = =C5/DATEDIF(E18,EOMONTH(E19,1),"m")*(DATEDIF(E18,EOMONTH(E19,1),"m")-(MONTH(TODAY())-MONTH(E18)))

This was working fine, up until this month, when it appears to now have wildly inflated the figure. Possibly something to do with the new calendar year?

Based on the date value in E19, there are 3 salary payments left this FY, yet the figure in C12 is much, much more than that.

Any ideas?

Thanks

 

[welshgasman]

Break each down into separate cells. Quite likely the year change is the issue, as you do not take it into account?
Calculate the year with YEAR() and multiple that by 12 to get the months, then add your Month() value, then do the math.

 

[chive90]

Break each down into separate cells. Quite likely the year change is the issue, as you do not take it into account?
Calculate the year with YEAR() and multiple that by 12 to get the months, then add your Month() value, then do the math.

Do you mean break down the formula in C12? It was a formula I found and adapted either from on here or online so I am not across exactly how it works if I'm honest.
If you could help break it down for me and possibly advise how I input a year element to it, that would be great.

 

[Tetra201]

See if the following helps:

=C5/DATEDIF(E18,EOMONTH(E19,1),"m")*(DATEDIF(E18,EOMONTH(E19,1),"m")-MOD(MONTH(TODAY())-MONTH(E18),12))

 

[chive90]

See if the following helps:

=C5/DATEDIF(E18,EOMONTH(E19,1),"m")*(DATEDIF(E18,EOMONTH(E19,1),"m")-MOD(MONTH(TODAY())-MONTH(E18),12))

That works brilliantly, thanks!
What does the MOD function do in this case?

 

[welshgasman]

That works brilliantly, thanks!
What does the MOD function do in this case?

TBH, I have no clue as to how that even works? and I have used Mod() in the past myself.

MOD function - Microsoft Support

This article describes the formula syntax and usage of the MOD function, which returns the remainder after number is divided by divisor. The result has the same sign as divisor

support.microsoft.com

 

[Tetra201]

In this particular case, the MOD function ensures that the difference between MONTH(TODAY()) and MONTH(E18) varies in the range of 0...11 and never goes negative.

Glad to have helped.

 

[welshgasman]

In this particular case, the MOD function ensures that the difference between MONTH(TODAY()) and MONTH(E18) varies in the range of 0...11 and never goes negative.

Glad to have helped.

No, I mean, I understood it to be the remainder after dividing by the divisor?
- 3 divided by 12 does not come to 9?, more like -3 + 12 ?

 

[MARK858]

No, I mean, I understood it to be the remainder after dividing by the divisor?
- 3 divided by 12 does not come to 9?, more like -3 + 12 ?

With a negative number you keep adding the Modulus (in this case 12) until it reaches a positive number i.e. -3 + 12 = 9

For example -11 mod 7 would be -11 + 7 + 7 = 3

a - b*INT(a/b) is what is actually used by Excel for calculating Mod(a, b), so with the -3 and 12

Book1
	F
26	-3
27	12
28
29	9
Sheet1

Cell Formulas
Range	Formula
F29	F29	=F26-F27*INT(F26/F27)

 

[welshgasman]

Thank you @MARK858