Date: days since 01.01.1900

[Peter2]

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

[Exaskryz]

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]

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

[SifJar]

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]

Thanks @Exaskryz for the original code and @SifJar for the modification.

[Peter2]

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