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
Date: days since 01.01.1900
Date: days since 01.01.1900
Peter (AHK Beginner) / Win 10 x64, AHK Version v1.1.33
Re: Date: days since 01.01.1900
EnvAdd can do what you want.
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.
Code: Select all
^7::
days:=41689
start:=19000101000000
EnvAdd, start, days, d
MsgBox % start
FormatTime, date, % start, MM/dd/yyyy
MsgBox % date
return
Re: Date: days since 01.01.1900
No interaction with, no usage of Excel - pure AHK. Let's see if the difference of the results can be solved ....Exaskryz wrote:...to interchange between Excel and AHK, ...
Peter (AHK Beginner) / Win 10 x64, AHK Version v1.1.33
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
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.
Re: Date: days since 01.01.1900
Thanks @Exaskryz for the original code and @SifJar for the modification.
Peter (AHK Beginner) / Win 10 x64, AHK Version v1.1.33
Re: Date: days since 01.01.1900
Additional info:
It seems that the decimal part of the input value needs an extra handling.
Example:
It seems that the decimal part of the input value needs an extra handling.
Example:
I made it this way (I'm sure it can be condensed to a half-liner ..)41689.75 -->> 19.02.2014 18:00
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 10 x64, AHK Version v1.1.33
Who is online
Users browsing this forum: No registered users and 158 guests