Formula Conversion Help

[deltran552]

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:

Code: Select all

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

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

[Helgef]

Hi. You are correct about floor. You can try to use this,

Code: Select all

trunc(f){
	return substr(f,1,instr(f*1.0,".")-1)
}

Possibly related: Mod function returning wrong value?

Good luck.

[jeeswg]

Can you give an/some example values where your script fails?

I believe that all you have to do is replace any use of Mod, with my JEE_Mod function.

Code: Select all

;[JEE_Mod function]
;Mod function returning wrong value? - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=14&t=29762&p=142063#p142063

q::
Date := 19000101000000
Loop, 50000
{
	CalYear := SubStr(Date, 1, 4)
	CalMonth := SubStr(Date, 5, 2)
	CalDay := SubStr(Date, 7, 2)

	;in this specific example you can use Floor instead of Trunc, since CalDay is positive
	Num1 := ((JEE_Mod(CalDay,10)*CalMonth)*100000)+((JEE_Mod(CalYear/10,10)*10)-Floor(10*JEE_Mod(CalYear/100, 10))*10)*10000+Floor(10*JEE_Mod(CalYear/100, 10))*1000+70+(Floor(CalDay/10))
	Num2 := ((Mod(CalDay,10)*CalMonth)*100000)+((Mod(CalYear/10,10)*10)-Floor(10*Mod(CalYear/100, 10))*10)*10000+Floor(10*Mod(CalYear/100, 10))*1000+70+(Floor(CalDay/10))

	;original code
	FirstFormulaSection := Mod(CalDay, 10) * CalMonth * 100000
	SecondFormulaSection := Floor((Mod((CalYear/10),10)*10)-(Floor(10*Mod((CalYear/100),10))*10))
	ThirdFormulaSection := Floor(10*Mod((CalYear/100),10))
	FourthFormulaSection := Floor(CalDay/10)
	SixDigitCode := Floor(FirstFormulaSection + SecondFormulaSection * 10000 + ThirdFormulaSection * 1000 + 70 + FourthFormulaSection) ;Floor() is used here to make the value a whole number with no decimals

	;replaced with JEE_Mod
	FirstFormulaSection := JEE_Mod(CalDay, 10) * CalMonth * 100000
	SecondFormulaSection := Floor((JEE_Mod((CalYear/10),10)*10)-(Floor(10*JEE_Mod((CalYear/100),10))*10))
	ThirdFormulaSection := Floor(10*JEE_Mod((CalYear/100),10))
	FourthFormulaSection := Floor(CalDay/10)
	SixDigitCode := Floor(FirstFormulaSection + SecondFormulaSection * 10000 + ThirdFormulaSection * 1000 + 70 + FourthFormulaSection) ;Floor() is used here to make the value a whole number with no decimals

	Num1 := Round(Num1), Num2 := Round(Num2)
	;if !(Num1 = Num2) || !(Num1 = SixDigitCode)
	if !(Num1 = SixDigitCode)
		Output .= Date "`t" Num1 "`t" Num2 "`t" SixDigitCode "`r`n"
	Date += 1, Days
}
MsgBox, % Date
Clipboard := Output
MsgBox, % Output
return

I tried creating a function that works like Excel's TRUNC function (I would test this thoroughly before any industrial use), and also functions to convert between Excel and AHK style dates:

Code: Select all

JEE_Trunc(vNum, vDigits=0)
{
	if !RegExMatch(vNum, "^-?\d+$|^-?\d+\.\d+$")
		vNum += 0
	if (vNum = "")
		return ""
	else if (vDigits = 0)
		return Floor(Abs(vNum)) * (vNum >= 0) ? 1 : -1
	else if (vDigits < 0)
		return Floor(Abs(vNum)/(10**Abs(vDigits))) * (10**Abs(vDigits)) * (vNum >= 0) ? 1 : -1
	else if (vDigits > 0)
	{
		vNum := RegExReplace(vNum, "^-?\K0+(?=[^0.]|0\.|0$)|\.0+$|\..*?\K0+$") ;remove leading/trailing zeros
		return RegExReplace(vNum, ".*\.\d{" vDigits "}\K.*", "") ;crop
	}
}

;==================================================

JEE_DateExcelToAhk(vNum, vFormat = "yyyyMMddHHmmss")
{
	vDate := 18991230
	vDate += vNum * 86400, Seconds
	FormatTime, vDate, % vDate, % vFormat
	return vDate
}

;==================================================

JEE_DateAhkToExcel(vNum)
{
	vDate -= 18991230, Seconds
	return vDate / 86400
}

[EDIT:] Here's some code to truncate to an integer, which is another way of doing what is done in Helgef's trunc function.

Code: Select all

q:: ;truncate to an integer (i.e. round to the nearest integer towards 0)
vNum := 123.456
MsgBox, % vNum := (vNum >= 0) ? Floor(vNum) : Ceil(vNum)
vNum := -123.456
MsgBox, % vNum := (vNum >= 0) ? Floor(vNum) : Ceil(vNum)
return

[deltran552]

Hi all,

I actually figured out what the problem with my script was (I haven't tried jeeswg's solution or Helgef's). In my original program, prior to adding a selectable date via a GUI calendar, I had included SetFormat, Float, 0.2 in the header. It seems that I accidentally left that out in my improved program.

Thanks for the prompt suggestions, I really appreciate them!