### Formula Conversion Help

Posted:

**12 Jul 2017, 18:12**Hi All,

I am having some trouble getting a value to calculate correctly in AHK, that is calculating correctly in MS Excel. I've converted the formula to what I *think* is correct given the difference in AHK commands and Excel formula functions, but what I'm finding is that one digit is off.

Here's the original formula from Excel:

And here's the AHK code. I broke it into chunks to make it more manageable:

I've used floor() because I don't think AHK has a function similar to int() or trunc() that the Excel formula is using. I do think it's important to note that trunc(-3.5) would return -3, while floor(-3.5) would return -4 (at least, this is my understanding on how floor() would work).

I'm not sure where I'm going wrong in this. I have another program with the exact same calculations to generate this code, the only difference in that program is that you can't select a new date (it's using DateTime and a_now to get the year, month, etc). In this program, I've used MonthCal.

Thanks in advance for the help.

I am having some trouble getting a value to calculate correctly in AHK, that is calculating correctly in MS Excel. I've converted the formula to what I *think* is correct given the difference in AHK commands and Excel formula functions, but what I'm finding is that one digit is off.

Here's the original formula from Excel:

Code: [Select all] [Download] GeSHi © Codebox Plus

=((MOD(DAY(B14),10)*MONTH(B14))*100000)+((MOD(YEAR(B14)/10,10)*10)-INT(10*MOD(YEAR(B14)/100, 10))*10)*10000+INT(10*MOD(YEAR(B14)/100, 10))*1000+70+(TRUNC(DAY(B14)/10))

And here's the AHK code. I broke it into chunks to make it more manageable:

Code: [Select all] [Expand] [Download] GeSHi © Codebox Plus

;Calculations for the six digit code

FirstFormulaSection := Mod(CalDay, 10) * CalMonth * 100000

MsgBox % FirstFormulaSection

SecondFormulaSection := Floor((Mod((CalYear/10),10)*10)-(Floor(10*Mod((CalYear/100),10))*10))

MsgBox % SecondFormulaSection

ThirdFormulaSection := Floor(10*Mod((CalYear/100),10))

MsgBox % ThirdFormulaSection

FourthFormulaSection := Floor(CalDay/10)

MsgBox % FourthFormulaSection

SixDigitCode := Floor(FirstFormulaSection + SecondFormulaSection * 10000 + ThirdFormulaSection * 1000 + 70 + FourthFormulaSection) ;Floor() is used here to make the value a whole number with no decimals

MsgBox %SixDigitCode%

I've used floor() because I don't think AHK has a function similar to int() or trunc() that the Excel formula is using. I do think it's important to note that trunc(-3.5) would return -3, while floor(-3.5) would return -4 (at least, this is my understanding on how floor() would work).

I'm not sure where I'm going wrong in this. I have another program with the exact same calculations to generate this code, the only difference in that program is that you can't select a new date (it's using DateTime and a_now to get the year, month, etc). In this program, I've used MonthCal.

Thanks in advance for the help.