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]

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)

It would appear to be the same with negative when the divisor is negative, as shown by the third example in my link.