convert common date formats (Excel / FileTime / Unix)

[jeeswg]

- 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
}

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

[BoBo]

[vvhitevvizard]

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

[Rafaews]

If you're getting your time a bit off:

- Note: The functions handle UTC, so you may need to add/subtract some hours to adjust for your time zone.

My little function that does that:

Code: Select all

CorrectTimeZone(DateTime)
{
	vTime := A_Now
	EnvSub, vTime, A_NowUTC, Hours
	
	EnvAdd, DateTime, vTime, Hours
	return DateTime
}