## Date: days since 01.01.1900

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

### Date: days since 01.01.1900

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
Posts: 2876
Joined: 17 Oct 2015, 20:28

### Re: Date: days since 01.01.1900

EnvAdd can do what you want.

Code: Select all

``````^7::
days:=41689
start:=19000101000000
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: 252
Joined: 21 Sep 2014, 14:38
Location: CH

### Re: Date: days since 01.01.1900

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

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: 252
Joined: 21 Sep 2014, 14:38
Location: CH

### Re: Date: days since 01.01.1900

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: 252
Joined: 21 Sep 2014, 14:38
Location: CH

### Re: Date: days since 01.01.1900

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