convert common date formats (Excel / FileTime / Unix)

Get help with using AutoHotkey and its commands and hotkeys
User avatar
jeeswg
Posts: 6904
Joined: 19 Dec 2016, 01:58
Location: UK

convert common date formats (Excel / FileTime / Unix)

24 Feb 2018, 12:29

- I've written some functions for converting common date formats:
Excel: days since 1900
FileTime: 100-nanosecond intervals since 1601
Unix: seconds since 1970
- I'm posting here in case anyone has any other suggestions for common date formats.
- At the moment, it's not entirely clear whether any special leap second handling is required:
Leap second - Wikipedia
https://en.wikipedia.org/wiki/Leap_second
- Note: The functions handle UTC, so you may need to add/subtract some hours to adjust for your time zone.

Code: Select all

q:: ;convert dates (Excel / FileTime / Unix)
vDate := A_Now
MsgBox, % vNum := JEE_DateAhkToExcel(vDate)
MsgBox, % JEE_DateExcelToAhk(vNum, "yyyy-MM-dd")
MsgBox, % vNum := JEE_DateAhkToFileTime(vDate)
MsgBox, % JEE_DateFileTimeToAhk(vNum, "yyyy-MM-dd")
MsgBox, % vNum := JEE_DateAhkToUnix(vDate)
MsgBox, % JEE_DateUnixToAhk(vNum, "yyyy-MM-dd")
return

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

;Differences between the 1900 and the 1904 date system in Excel
;https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel
;a serial number that represents the number of elapsed days since January 1, 1900

;vNum: blank value means now
JEE_DateExcelToAhk(vNum:="", vFormat:="yyyyMMddHHmmss")
{
	if !(vNum = "")
		vDate := DateAdd(18991230, vNum*86400, "Seconds")
	if (vNum = "") || !(vFormat == "yyyyMMddHHmmss")
		return FormatTime(vDate, vFormat)
	return vDate
}

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

;vDate: blank value means now
JEE_DateAhkToExcel(vDate:="")
{
	return DateDiff(vDate, 18991230, "Seconds") / 86400
}

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

;FILETIME structure (Windows)
;https://msdn.microsoft.com/en-us/library/windows/desktop/ms724284(v=vs.85).aspx
;number of 100-nanosecond intervals since January 1, 1601 (UTC)

;vNum: blank value means now
JEE_DateFileTimeToAhk(vNum:="", vFormat:="yyyyMMddHHmmss")
{
	if !(vNum = "")
		vDate := DateAdd(1601, vNum//10000000, "Seconds")
	if (vNum = "") || !(vFormat == "yyyyMMddHHmmss")
		return FormatTime(vDate, vFormat)
	return vDate
}

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

;vDate: blank value means now
JEE_DateAhkToFileTime(vDate:="")
{
	return DateDiff(vDate, 1601, "Seconds") * 10000000
}

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

;Unix time - Wikipedia
;https://en.wikipedia.org/wiki/Unix_time
;the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus the number of leap seconds that have taken place since then

JEE_DateUnixToAhk(vNum:="", vFormat:="yyyyMMddHHmmss")
{
	if !(vNum = "")
		vDate := DateAdd(1970, vNum, "Seconds")
	if (vNum = "") || !(vFormat == "yyyyMMddHHmmss")
		return FormatTime(vDate, vFormat)
	return vDate
}

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

;vDate: blank value means now
JEE_DateAhkToUnix(vDate:="")
{
	return DateDiff(vDate, 1970, "Seconds")
}

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

;commands as functions (AHK v2 functions for AHK v1) - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=37&t=29689

DateAdd(DateTime, Time, TimeUnits)
{
    EnvAdd DateTime, %Time%, %TimeUnits%
    return DateTime
}
DateDiff(DateTime1, DateTime2, TimeUnits)
{
    EnvSub DateTime1, %DateTime2%, %TimeUnits%
    return DateTime1
}
FormatTime(YYYYMMDDHH24MISS:="", Format:="")
{
    local OutputVar
    FormatTime OutputVar, %YYYYMMDDHH24MISS%, %Format%
    return OutputVar
}

;==================================================
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
vvhitevvizard
Posts: 299
Joined: 25 Nov 2018, 10:15
Location: Russia

Re: convert common date formats (Excel / FileTime / Unix)

20 Jan 2019, 20:14

jeeswg wrote:
24 Feb 2018, 12:29
- At the moment, it's not entirely clear whether any special leap second handling is required:
Any special handling would be required only if there r formats having its base starting from year 1972.

Return to “Ask For Help”

Who is online

Users browsing this forum: Albireo, pn4265 and 248 guests