Date as Text String to Date/Time Variable and Decimal Time FYI

[Yehnfikm8Gq]

This should really go in 3rd Party Tools but that is apparently 3x only.

 

There is currently no way to convert a Date text string to Date/Time Variable other than at the time of writing the macro. The ME Pro inputs are all manual (calendar pop-up or updown box). I wrote a macro that does it using the Current Date and Time and a second version that uses 1st Jan 2000. They both give the same results - the date base is irrelevant for most users.

 

You can input the Time of Day or if you enter nothing, it assumes mid-day, 12pm. The macro converts a date in the format mm/dd/yyyy and time in format hh/mm/ss, 24 hour clock, into a Date/Time Variable and Decimal time. It is only valid as far as the calendar used by ME goes. Country of user could be an issue which is why this is currently unavailable in ME. As far as I can see, it should be as accurate as the Date/Time variable is. The year cannot be before 1900 as set by ME. The macro method is very crude but seems to work for all dates tested by author.

 

The macros are currently written for user input by prompt and output for initial user testing by Text Box Display. Input can alternatively be made by removing the input dialogs and running the macro from another macro and transferring values; from the Clipboard; or setting values with Set Variable.

 

The output displays can be removed. The results are in Date/Time Variable %dSubtracted DateTime% and Decimal time %decSubtracted DateTime%. To change the names if desired there is no provision for copying Date Variables. Use Variable Modify Date/Time, adjust by 0 (milliseconds?) and specify the output variable with a new name.

 

The macro contains some basic input verification for year, month, day, hour, minute, second. This is my own creation and is not exhaustive (for example it does not check if any characters are alphabetical). If the user has a good date verification macro, the subroutine can be removed and the macro redirected to the user's (subject to getting variables in required format).

 

The macros also have some result verification lines which are currently disabled. If enabled, the Decimal Time is converted back to Date/Time Variable to show the results are (hopefully) correct and agree with the user input data. Note that midnight Date/Time Variables do not exhibit a time, just the date.

 

Short versions of the macros are included in which all extraneous code including date verification has been removed. They still contain the input/output display commands but the output is disabled (enable for testing).

 

As a result of the way the macro was developed, it is possible to extract the output data part-way through the (currentday) macros which will give output at the target date but the time of day the macro is actually run. This may be useful if it is desired to know the Date/Time Variable or Decimal time an exact number of whole days from the macro time. The variables are %dFromNowDateTime% and %decFromNowDateTime% and are independent of any Time of Day entered at the start of the macro.

 

Month/day/year always seems to be the North American standard so that was used. To swop to dd/mm/yyyy all the variables with index [1] could be swopped for variables index [2]. Time of Day variable will be unaffected. Alternatively convert format prior to entering the macro.

 

Date String to Date Variable: Full macro using 1st Jan 2000 as base, includes date verification

 

Date String to Date Variable (short): Short macro using 1st Jan 2000 as base

 

Date String to Date Variable (currentday): Full macro using Current Date/Time as base, includes date verification and can provide Date/Time and Decimal Time Variables exact days to the minute from time of running macro (eg set required date to 2/15/2007 and run macro at 3.15pm, the Date/Time Variable and Decimal Time will be for 2/15/2007 at 3.15pm)

 

Date String to Date Variable (short currentday): Short macro using Current Date/Time as base and can provide Date/Time and Decimal Time Variables exact days to the minute from time of running macro

 

The first two are slightly shorter code and would suit most users. There are some audible alarm files that are in the Windows/Media path. Change or remove to suit need.

 

They are offered as-is. Test away. Please provide feedback of any problems.

 

 

[Cory]

Great trick. I would have never thought of using that. I'll have to bear it in mind if I have any quick and dirty needs.

 

If anyone is interested I do have an actual macro subroutine which calculates the date and time to a serial (decimal) value. The only benefit I see to John's method is that mine can be completely invisible which might be imperative in some macros, especially if they're doing a bunch of ops in a repeat. That and it's very quick. Mine also works on any date, even before 1900 and applies the proper leap year rules. EG every 4 years unless divisible by 100 unless divisible by 400.

 

It sure will be nice when ISS adds this capability to the time commands natively. Maybe ISS could code my algorithm if Delphi doesn't have the native support. I found a cool mathematical trick to do the calculation instead of relying on a "count up" method.

[Yehnfikm8Gq]

My macro is not intended to be visible either. The only reason I added manual I/O capability was so that users can test to their satisfaction and then delete the dialogs. The speed of operation varies since it works from Jan 1st in the target year. Anyone with a Christmas delivery database could modify for Jan 1st of the following year and count back!

[Cory]

Oh, then I'll have to look at it more closely. Thanks.

[Yehnfikm8Gq]

At the start of the code are comments about inputting data like

"Input here %tTarget Date% and %tTarget ToD% by whatever means"

 

and outputs as

"Date/Time Variable is in %dSubtracted DateTime%

"Decimal Time is in %decSubtracted DateTime%

 

You strip out what is not needed. The "short" versions do not have the variable description lists nor any date input checking.