Page 1 of 1

FormatTime Issues

Posted: 02 Apr 2019, 18:57
by 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"
		MsgBox % A_Now


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.

Re: FormatTime Issues

Posted: 02 Apr 2019, 19:34
by 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
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

Re: FormatTime Issues  Topic is solved

Posted: 02 Apr 2019, 19:36
by just me

Code: Select all

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)

Re: FormatTime Issues

Posted: 02 Apr 2019, 19:50
by 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.

Re: FormatTime Issues

Posted: 03 Apr 2019, 10:41
by 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.

Re: FormatTime Issues

Posted: 04 Apr 2019, 17:23
by 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.