FormatTime Issues Topic is solved

[Monoxide3009]

I have tried to research FormatTime and went over the wiki several times, but I am having a hard time utilizing this function.

My overall goal is to take a date from excel, and compare it to the current date to get a day difference. My problem seems to stem from the format of each data point being different.

Today using A_Now is coming out as 20190402182124 and the format of the date in my excel sheet is 4/5/2019 12:00:00 AM. The spreadsheet cannot be manipulated, so any changes need to be done through AHK. I am expecting the outcome to be 3 days using the above example.

I have tried to convert the excel data to a better format, but it seems to be reverting to the current date, instead of changing the format, and I am not sure if I am mistaken on how the function works, or if I am just writing it incorrectly.

I have the message boxes just for checking to see if I am getting what I expect. Though the variable after the formattime function keeps reverting to the current date.

Code: Select all

		TARGET_DELIVERY_DATE := "4/5/2019 12:00:00 AM"
			RegExMatch(TARGET_DELIVERY_DATE,"(\d+\/\d+\/\d+).*",TARGET_DELIVERY_DATE_CONVERTED)
			TARGET_DELIVERY_DATE_CONVERTED := TARGET_DELIVERY_DATE_CONVERTED1
				
		MsgBox % TARGET_DELIVERY_DATE_CONVERTED
		FormatTime, TARGET_DELIVERY_DATE_CONVERTED,Mdyyyy, yyyyMMdd
		MsgBox % TARGET_DELIVERY_DATE_CONVERTED
		MsgBox % A_Now

		EnvSub, TARGET_DELIVERY_DATE_CONVERTED, %A_Now%, days
		MsgBox, %TARGET_DELIVERY_DATE_CONVERTED%

EDIT ---- I would still like to see if there is a way to fix this issue as stated above, but once I wrote everything out I just had the thought "why not just have AHK change the format of the spreadsheet through a comobject?" Which I did.

Code: Select all

xlApp.range("O:O").NumberFormat := "yyyymmdd"

I havent yet confirmed if this will work for what I need, but seeing as the issue was getting the proper format, I dont forsee any further issues.

[jeeswg]

Here's some code to convert dates.

Code: Select all

q:: ;convert AM/PM dates
vDate := "4/5/2019 12:00:00 AM"
;vDate := "4/5/2019 12:00:00 PM"
;vDate := "4/5/2019 06:00:00 AM"
;vDate := "4/5/2019 06:00:00 PM"
if !RegExMatch(vDate, "^\d+/\d+/\d+ \d+:\d+:\d+ [AP]M$")
{
	MsgBox, % "error: nonstandard date:`r`n" vDate
	return
}
vDate2 := RegExReplace(vDate, "^(\d+)/(\d+)/(\d+) (\d+:\d+:\d+).*$", "$3:$2:$1:$4")
oDate := StrSplit(vDate2, ":")
;12 AM -> 0
;1-11 AM -> 1-11 (unchanged)
;12 PM -> 12 (unchanged)
;1-11 PM -> 13-23 (add 12)
if RegExMatch(vDate, "AM$") && (oDate.4 = 12)
	oDate.4 := 0
else if RegExMatch(vDate, "PM$") && !(oDate.4 = 12)
	oDate.4 += 12
vDate := Format("{:04}{:02}{:02}{:02}{:02}{:02}", oDate*)
MsgBox, % vDate
return

[just me]

Code: Select all

#NoEnv
TARGET_DELIVERY_DATE := "4/5/2019 12:00:00 AM"
RegExMatch(TARGET_DELIVERY_DATE,"(\d{1,2})/(\d{1,2})/(\d{4})", TDD)
TARGET_DELIVERY_DATE_CONVERTED := Format("{:04}{:02}{:02}", TDD3, TDD1, TDD2)
MsgBox % TARGET_DELIVERY_DATE_CONVERTED

[Monoxide3009]

Thanks guys. I somewhat understand jeeswg's (and I know you have answered several of my questions before), but just me's response seems a lot more concise. I think the big key is the Format() function you both used; I hadnt seen that before.

I am going to work with both the excel conversion and the format changes and see which one feels better.

[sinkfaze]

Code: Select all

xl :=	ComObjActive("Excel.Application"), xl.screenUpdating :=	0
xl.Range("O:O").NumberFormat :=	"yyyymmdd""000000"""
xl.screenUpdating :=	1

Remember that once you do this conversion you'll have to extract the timestamp from the cells using the Text property, not the Value property.

[jeeswg]

- just me's post is fine, but just FYI, it uses the deprecated RegExMatch variable mode to produce multiple variables, cf. the more forwards compatible RegExMatch object mode.
- Also, it doesn't handle converting 12-hour to 24-hour format.