Date/Time conversion to decimal?

[terrypin]

I haven't used Date/Time variables much so maybe this has an obvious explanation. I'm trying to calculate the time in seconds between two events and getting a result I don't understand.

 

Expressed as text the times are:

tEndTime = 22:14:08

tStartTime = 22:14:01

which correctly shows that the macro took 7 seconds.

 

Expressed as Date/Time variables those are:

EndTime = 17/03/2010 22:14:08

StartTime = 17/03/2010 22:14:01

which again are correct.

 

But after using the commands:

 

Convert Date/Time to Decimal: %StartTime% => %dStartTime%

Convert Date/Time to Decimal: %EndTime% => %dEndTime%

 

I don't understand the result:

 

dEndTime = 40254.9264879861

dStartTime = 40254.9264107639

 

What units are these?

 

And why, after using these final commands

 

Variable Modify Decimal: %dDuration% = %dEndTime% - %dStartTime%

Variable Modify Decimal %dDurationRounded%: Copy Value (%dDuration%)

Variable Modify Decimal: Round %dDuration% to 1 decimal places

Text Box Display:

do I get

 

dDurationRounded = 7.72222192608751E-5

dDuration = 0 ?

 

--

Terry, East Grinstead, UK

[paul]

Convert Date/Time to Decimal: %StartTime% => %dStartTime%

Convert Date/Time to Decimal: %EndTime% => %dEndTime%

 

I don't understand the result:

 

dEndTime = 40254.9264879861

dStartTime = 40254.9264107639

 

What units are these?

The number of days and part days since some particular starting point - usually something like Midnight on 1st January 1900. This is a very standard way to represent time.

Microsoft itself can't seem to agree on a universal starting point! Sometimes MS uses 1/1/1900, sometimes 1/1/1901, and sometimes 31/12/1899, depending on the product you're using.

[paul]

Convert Date/Time to Decimal: %StartTime% => %dStartTime%

Convert Date/Time to Decimal: %EndTime% => %dEndTime%

But why are you converting to decimal? Date/time variables support arithmetic. Displaying a date/time variable displays a readable result.

And if you set dtv1 to now, dtv2 to now + 7 seconds, dtv3 to dtv2 - dtv1, and display all 3 variables, you get 30/12/1899 00:00:07 as the value of dtv3 (which confirms that MEP's starting date and time is midnight on 30/12/1899).

 

MEP lacks quite a lot of date/time functions, e.g. DateDiff which would allow you to return the difference between 2 date/time variables in a time unit of your choice (days, hours, seconds, etc.).

 

So, in order to make your function work, I'd do as follows:

Use date/time variables to record your start and end times
Subtract start time from end time into a 3rd date/time variable (dRes)
Convert dRes to decimal (eRes)
Convert result to seconds (eRes * 86400) into a decimal variable (eResConv)
Truncate eResConv to integer (nRes), OR round eResConv to integer (more complex)
nRes should not hold your answer (in seconds)

I haven't thought about this too much, so there may be a more elegant solution.

[Yehnfikm8Gq]

There are a number of missing Date/Time functions as Paul mentioned. One of the most glaring is that 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 inputs are all manual (calendar pop-up or updown box). ISS responded to me that this is due to issues with the user's country.

 

I've written a macro that does it (which I've split into 4 macros), for North America at least. I'll try to post them shortly.

 

Another issue is that there is no way to copy a Date/Time Variable. The only way I've found is to add 0 milliseconds (I guess any time unit would do) and you can put the result in a new Date/Time variable. It may no seem useful but I use it in the forementioned macros.

 

Just mentioning in passing, sorry to go OT.

[paul]

I've written a macro that does it (which I've split into 4 macros), for North America at least. I'll try to post them shortly.

Why write your macros for such a small minoritry group? The population of North America accounts for just 4.536363490514687% of the world! Most people definitely don't use such a silly date format as mm/dd/yyyy!

[terrypin]

But why are you converting to decimal? Date/time variables support arithmetic. Displaying a date/time variable displays a readable result.

And if you set dtv1 to now, dtv2 to now + 7 seconds, dtv3 to dtv2 - dtv1, and display all 3 variables, you get 30/12/1899 00:00:07 as the value of dtv3 (which confirms that MEP's starting date and time is midnight on 30/12/1899).

 

MEP lacks quite a lot of date/time functions, e.g. DateDiff which would allow you to return the difference between 2 date/time variables in a time unit of your choice (days, hours, seconds, etc.).

 

So, in order to make your function work, I'd do as follows:

Use date/time variables to record your start and end times
Subtract start time from end time into a 3rd date/time variable (dRes)
Convert dRes to decimal (eRes)
Convert result to seconds (eRes * 86400) into a decimal variable (eResConv)
Truncate eResConv to integer (nRes), OR round eResConv to integer (more complex)
nRes should not hold your answer (in seconds)

I haven't thought about this too much, so there may be a more elegant solution.

 

Thanks Paul. I'd completely missed the fact that Date/time variables support arithmetic. Hence my misguided conversions to decimal

 

Date/Time only seems to allow 1 second precision. So for timing macros (I'd like 1 dp of seconds) I reckon it might be best to simply manipulate text & decimal variables. Of course, with a couple of extra MEP commands, it would be a piece of cake.

 

BTW, do I take it from your example that you've standardised on 'd' to prefix Date/Time variables and 'e' for Decimal? Or do you revert to 'd' for Decimal when there are no D/Ts around? I'm thinking of using 'd' and 'dt' respectively.

 

--

Terry, East Grinstead, UK

[terrypin]

Here's my macro for converting the current time into seconds and milliseconds, i.e: SSSSS.sss. It ignores the date, as it's intended principally for calculating the duration of fairly short events, typically seconds or minutes, or at a maximum 24 hours (OK, 86399.000 seconds).

 

ConvertCurrentTimeToSeconds.mex

 

Running this at the start and end of any event makes it easy to calculate its duration.

 

This wouldn't be needed if the ME Pro option to extract Milliseconds for the Variable Modify Date / Time command actually worked! But of course it can't, because the data doesn't include milliseconds.

 

--

Terry, East Grinstead, UK

[paul]

Here's my macro for converting the current time into seconds and milliseconds, i.e: SSSSS.sss. It ignores the date, as it's intended principally for calculating the duration of fairly short events, typically seconds or minutes, or at a maximum 24 hours (OK, 86399.000 seconds).

Did you ever purchase the PGM Library? I wrote a set of functions, invoked by a hotkey, which would do this and more:

- you can time the entire macro currently being edited

- you can time an entire macro from Explorer

- you can time any subset of selected contiguous lines within the currently edited macro

Its precision is milliseconds.

[paul]

BTW, do I take it from your example that you've standardised on 'd' to prefix Date/Time variables and 'e' for Decimal? Or do you revert to 'd' for Decimal when there are no D/Ts around? I'm thinking of using 'd' and 'dt' respectively.

I hardly use date/time variables, so, realizing I'd already used d for date/time, I simply used the next relevant letter for decimal, i.e. the 2nd letter instead of the first!

Curiously, in VB I always use 3-letter mnemonics, e.g. int, dbl, str. But I seem to prefer single letters in ME, presumably because you always have to type the full name (whereas VB has all sorts of IDE tricks to avoid having to type stuff in).

[terrypin]

Did you ever purchase the PGM Library? I wrote a set of functions, invoked by a hotkey, which would do this and more:

- you can time the entire macro currently being edited

- you can time an entire macro from Explorer

- you can time any subset of selected contiguous lines within the currently edited macro

Its precision is milliseconds.

 

No, where is it? I enquired about it in an email to Joe 12th March 2009, because the link to it http://www.pgmacros.com/members/book in his book didn't work. But apart from confirming that, he gave me no new link!

 

I'm at http://pgmacros.invisionzone.com, where the only non-private link that looks relevant is 'Professional Grade Macros / Macros LLC'. But that doesn't appear to include a summary describing the library, its cost and ordering process.

 

--

Terry, East Grinstead, UK

[paul]

No, where is it? I enquired about it in an email to Joe 12th March 2009, because the link to it http://www.pgmacros.com/members/book in his book didn't work. But apart from confirming that, he gave me no new link!

 

I'm at http://pgmacros.invisionzone.com, where the only non-private link that looks relevant is 'Professional Grade Macros / Macros LLC'. But that doesn't appear to include a summary describing the library, its cost and ordering process.

Although it converts to MEP without error, I'm not convinced that all the functionality works, and we've never spent the time and effort needed to ensure 100% compatibility.

I'll take a look at the macro timing functions tomorrow and see if anything can be resuscitated.

[kevin]

This wouldn't be needed if the ME Pro option to extract Milliseconds for the Variable Modify Date / Time command actually worked!

Extracting milliseconds from a Date/Time variable works fine.

 

But of course it can't, because the data doesn't include milliseconds.

The Date/Time variables do include milliseconds.

 

Here is a sample macro demonstrating their use.

SampleElapsedSecondsAndMilliseconds.mex

[paul]

Extracting milliseconds from a Date/Time variable works fine.

 

The Date/Time variables do include milliseconds.

 

Here is a sample macro demonstrating their use.

Quite right - thanks.

[terrypin]

Extracting milliseconds from a Date/Time variable works fine.

 

The Date/Time variables do include milliseconds.

 

Here is a sample macro demonstrating their use.

 

Thanks Kevin, my mistake, sorry.

 

I was mislead by being unable to display the miiliseconds. Am I right? That without further manipulation I can only display

20/03/2010 10:29:49

even though the variable may contain 20/03/2010 10:29:49.123 ?

 

Also, if I add it to the displayed results, I see the rather odd result

ElapsedTime = 30/12/1899 00:00:01

 

Does that just mean that Date/variable is measured with that date as its zero?

 

--

Terry, East Grinstead, UK