Date: days since 01.01.1900

Get help with using AutoHotkey and its commands and hotkeys
Peter2
Posts: 243
Joined: 21 Sep 2014, 14:38
Location: CH

Date: days since 01.01.1900

14 Feb 2016, 12:39

Usually MS Excel calculates dates with "days since 01.01.1900" - eg. 19.02.2014 is day 41689" (=> approx. 114 years x 365 days = 41610)

Is there a function in AHK to convert numbers to date: 41689 -->> 19.02.2014?

Have a fine week!

Peter
Peter (AHK Beginner) / Win 7 x64, AHK Version v1.1.22.xx
User avatar
Exaskryz
Posts: 2876
Joined: 17 Oct 2015, 20:28

Re: Date: days since 01.01.1900

14 Feb 2016, 12:56

EnvAdd can do what you want.

Code: Select all

^7::
days:=41689
start:=19000101000000
EnvAdd, start, days, d
MsgBox % start
FormatTime, date, % start, MM/dd/yyyy
MsgBox % date
return
This seems to return Feb 22nd 2014 though. So it seems to differ from MS Excel's. If you want to be able to interchange between Excel and AHK, you'll need to use Excel's formula for calculating it and figure out where the discrepancy lies.
Peter2
Posts: 243
Joined: 21 Sep 2014, 14:38
Location: CH

Re: Date: days since 01.01.1900

14 Feb 2016, 13:38

Exaskryz wrote:...to interchange between Excel and AHK, ...
No interaction with, no usage of Excel - pure AHK. Let's see if the difference of the results can be solved ....
Peter (AHK Beginner) / Win 7 x64, AHK Version v1.1.22.xx
SifJar
Posts: 398
Joined: 11 Jan 2016, 17:52

Re: Date: days since 01.01.1900

14 Feb 2016, 18:59

Code: Select all

^7::
days:=41689
start:=19000101000000
EnvAdd, start, days-2, d ;subtract 2 from days due to Excel bug
MsgBox % start
FormatTime, date, % start, dd/MM/yyyy ;fix formatting to format in OP
MsgBox % date
return
You need to subtract 2 from the days; 1 because you start on January 1st, rather than January "0th", and 1 because of a bug dating all the way back to Lotus 1-2-3, detailed here: https://support.microsoft.com/en-us/kb/214326 (basically, that program assumed 1990 was a leap year, but it wasn't, and for compatibility MS kept that "bug" intentionally in Excel).

Reference: http://www.kirix.com/stratablog/excel-d ... -from-1900

EDIT: Alternative solution is to decrease the start date by two days i.e. start:=18991230000000. Both are equivalent and it makes little to no difference which you use. I guess changing the start value would be technically slightly more efficient as you have one less calculation, but you'd have to be doing a massive amount of iterations for it to make any difference I'm quite sure.
Peter2
Posts: 243
Joined: 21 Sep 2014, 14:38
Location: CH

Re: Date: days since 01.01.1900

15 Feb 2016, 02:10

Thanks @Exaskryz for the original code and @SifJar for the modification.
Peter (AHK Beginner) / Win 7 x64, AHK Version v1.1.22.xx
Peter2
Posts: 243
Joined: 21 Sep 2014, 14:38
Location: CH

Re: Date: days since 01.01.1900

15 Feb 2016, 14:37

Additional info:

It seems that the decimal part of the input value needs an extra handling.

Example:
41689.75 -->> 19.02.2014 18:00
I made it this way (I'm sure it can be condensed to a half-liner ..)

Code: Select all

dez_datum := 41689.750000
tage := Floor(dez_datum)
stunden := (dez_datum - tage) * 24
start:=19000101000000
EnvAdd, start, tage-2, d ;subtract 2 from days due to Excel bug
EnvAdd, start, stunden, H ; add calculated hour-value from decimal part
FormatTime, datum, % start, dd.MM.yyyy, HH:mm
Peter (AHK Beginner) / Win 7 x64, AHK Version v1.1.22.xx

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], don_dolarson, flyingDman, Zoanthario and 205 guests