Bluepoint Design

Macro Express Pro has many wonderful date time variable commands but unfortunately none to induct text dates and times into them. This macro will take 6 global string variables for year, month, day, hour, minute, and second (which can have a decimal for fractions of a second) and returns a decimal variable with the result. I figure users would rather get a decimal variable back and convert it to a time variable of their naming. All global variables are prefixed with "TST " so it's unlikely to step on one of your vars. They are in the format of "TST Year" and will need to be defined and set in your macro. This macro will then return "TST Serial DateTime" as a decimal. Obviously you must define all these variables in your parent macro.

Given the variety of date formats I've opted to make parsers a separate macro subroutine or to even do it in the parent macro. For instance the Date/Time from file creates text vars for each date parameter so you can simply plug them in there. You can see some of my parsers here.

The logic of this macro appears to be perfect. The main hurdle was the Leap Day rules for the Gregorian Calendar because the actual logic behind leap day is a 4, 100, and 400 year rule. I could have done a run thru process of building a date but opted to do a mathematical solution instead. It has proven to be very fast. To be certain I created a test macro and tested all days from 12/30/1899 to 1/1/3000.

Excel users beware, Delphi serial time and Excel serial time part company back in the early 1900's so if you have dates that go back that far you need to make an adjustment. You can read about it here.

See also:

1. Date Time Parsers
2. Understanding Serial Time
3. Day of Year