Date/Time Modification Dysfunction

[stevecasper]

  12/30/2008 6:52:14 PM 
+ 12/30/2008 6:52:13 PM
-----------------------------
 1/1/2118 1:44:27 PM

Confusing!

 

The best I can figure is the time adds up like time should. 13+14=27. 52+52 = 1:44. 6+6+1(carried) = 13.

13:00 = 1:00. I was confused by the fact that the result is PM, but then I though that maybe it's really calculating in 24 hr

time.

 

6:52 PM is 18:52. So 18:52 + 18:52 = 37:44, right? Subtracting 24 hours leaves us with 13:44 (1:44 PM). Ok so that

works.

 

But we've added only 1 day.

 

(12/30/2008)*2 somehow gives us 1/1/2118.

 

2008+2008 = 4016, so it's not a direct addition of the years.

12+12 = 24 (2 years) would bring 2008 to 2010, again, not a direct addition of the months.

 

I'm at a complete loss as far as comprehending how the Modify Date/Time function works, or in what way its current fun-

tionality could be of any possible use.

 

Subtracting is also a joke.

 

   12/30/2008 7:10:41 PM
-  12/30/2008 7:10:38 PM
------------------------------
  12/30/1899 12:00:03 AM

 

What I thought this command would do is help do calculations of the passage of time. Unfortunately, there is no way to

capture the time without also capturing the date (Date/Time modifications require D/T variables, which require date and

time).

 

This way one could figure out how much time elapsed between one D/T capture and another (which would, admittedly) only

require the use of the subtract function. I don't know why anybody would need to add one captured D/T to another.

 

Interesting note: If you subtract a D/T variable from itself, the result is the creepy 1899 date with no time:

   12/30/2008 7:10:41 PM
-  12/30/2008 7:10:41 PM
------------------------------
  12/30/1899

 

After my tests, I am not convinced there is any actual bug associated with this function. I just don't think I understand what

its point is. The Help files weren't very useful for understanding function, either. Can anybody illuminate me, please?

[Cory]

You have to understand that time is decimal. But zero isn't the time of Jesus' birth. In almost all number systems like Excel 1 is 1/1/1900. That means today is 39812, roughly 108 years. We had a great thread on this in the beta forum. Again, I think ISS is deficient in supplying us with proper descriptions.

 

One caveat I must toss out is that they use the 1900 date system as opposed to the 1904 date system. In Excel you can choose either. But you need to take care with MEP because it uses a slight tweak on the 1900 system. We all know the leap year rules, right? Or do you? Well when Lotus wrote 1-2-3 in the early days of computing they made a mistake and assumed the normal 4 year rule. But they didn't account for the 100 year rule! You see if a year is evenly divisible by 100 you don't observe a leap year. Well they missed that and thus they have a 2/29/1900 that should not exist. MS, in their infinite wisdom, decided they would rather be compatible with the Lotus format for competitive reasons so now we're stuck with it. So for dates before 2/28/1900 we have a problem. Now MEP is Delphi and they had a strange fix. Essentially they have a negative one rule so that they can work both ways.

 

Now you would think this isn't important but it can be when you do calculations. For instance if we want to calculate a day of week by using a remainder we need to take care. And, as in your example, additions and other manipulations care. Anyway I hope this helps.

[Cory]

Oh, that 12/30/1899 thing is correct. That's the Delphi negative one rule I was talking about. In MS land zero is 12/31/1899 2400 (same as 1/1/1900 0000) but in Delphi zero equates to 12/30/1900.

[stevecasper]

Oh, that 12/30/1899 thing is correct. That's the Delphi negative one rule I was talking about. In MS land zero is 12/31/1899 2400 (same as 1/1/1900 0000) but in Delphi zero equates to 12/30/1900.

Cory, you may just be smart/tech-educated enough to make this Date/Time thing work.

 

Any chance you could give an example of when a person would want to know what "today at 5:45:01 PM + tomorrow at

3:42:32 AM" equals? (or any arbitrary dates). I mean, they went to a lot of trouble to create the modify D/T command, and

God bless 'em for it. I am certain that they wouldn't have done so if there weren't a pretty global desire for it. A basic real-

world example would probably help me figure out whether or not I would ever need it in my life.

 

And here all I wanted to do was calculate the difference between two separate times (I know I can build a macro that does it

using a relatively lot of code, but I was hoping this would do it for me and be easy).

[terrypin]

I too am having trouble getting anywhere with Date/Time. But in any case it seems to me that ME's Date/Time function is very limited

compared to say Excel, which would be my obvious choice of application for such calculations. In Excel:

=INT(A2-A1) would give you the number of whole days between two date/ time values (which you would typically format as Number or General).

=MOD(A2-A1,1) would give you the remaining time (format with Custom to say hh:mm:ss).

 

Nevertheless it really should be pretty trivial to do this in ME, but I'm still struggling.

I haven't used the Date/Time functions before this discussion, so I expect there may be some glaringly obvious points I'm missing.

One thing that threw me for a while was that under Variables the list of commands includes 'Date/Time', which for consistency I think should be called 'Variable Set Date/Time' (or, better, change the entire list to get rid of the redundant word 'Variable' in every command).

 

Another stumbling block I encountered while trying to write the following simple macro to subtract two dates was that ME wouldn't let me use the name I'd used earlier but mistakenly assigned as a text variable, even though I'd deleted all commands and started again.

 

Anyway, here's my code as it currently stands:

 

// Enter two date/time values into appropriately named Date/Time variables

Date/Time: Set %Earlier Date% to "10/11/2008 16:48:00"

Date/Time: Set %Later date% to "11/12/2008 16:48:00"

Variable Modify Date/Time: %Result% = %Later date% - %Earlier Date%

Text Box Display:

 

<COMMENT Value="Enter two date/time values into appropriately named Date/Time  variables"/>
<DATE/TIME Flags="\xB1" Date="10/11/2008 16:48:00" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Earlier Date%" IsDateVar="TRUE"/>
<DATE/TIME Flags="\xB1" Date="11/12/2008 16:48:00" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Later date%" IsDateVar="TRUE"/>
<VARIABLE MODIFY DATE/TIME DateVar="%Result%" Option="\x01" LeftVar="%Later date%" RightVal="%Earlier Date%" UseInteger="TRUE" MathOpt="\x00"/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n{\\colortbl ;\\red128\\green0\\blue0;}\r\n\\viewkind4\\uc1\\pard\\cf1\\b\\f0\\fs16 Result = \\f1 %Result%\\cf0\\b0 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

 

That gave me the following strange result:

Result = 29/01/1900 23:59:59

 

 

Bottom line: all very confusing. A few worked examples in Help would be good. Or a few here in the forum from users who have mastered it.

 

(BTW, these new formating features sure do make the Codebox entries here long! Is there any clever forum feature to wrap them?)

 

--

Terry, East Grinstead, UK

[stevecasper]

I too am having trouble getting anywhere with Date/Time. But in any case it seems to me that ME's Date/Time function is very limited

compared to say Excel, which would be my obvious choice of application for such calculations. In Excel:

=INT(A2-A1) would give you the number of whole days between two date/ time values (which you would typically format as Number or General).

=MOD(A2-A1,1) would give you the remaining time (format with Custom to say hh:mm:ss).

Interesting stuff, I've never used Excel for time modifications. I'm going to have to play around with that.

Another stumbling block I encountered while trying to write the following simple macro to subtract two dates was that ME wouldn't let me use the name I'd used earlier but mistakenly assigned as a text variable, even though I'd deleted all commands and started again.

I had this same problem (discussed at painful length in another post). Turns out you just have to make sure that you've "Added" the

variable under the correct variable-type.

(BTW, these new formating features sure do make the Codebox entries here long! Is there any clever forum feature to wrap them?)

You know, I stopped using the built-in [ codebox ] tags and started using [ code ] tags. There is a little difference, but I don't know that

it affects the wrap-around.

[Cory]

An example of adding dates? How about finding the average date of birth of a group of people? Add all the DoBs and divide by the number of people to find the mean. There are all kinds of things like this. Perhaps you want to add a variable number of days to a value a user gave you in a macro and you want to round up to the next business day? You don't know what day you will start with or what number of days the user will choose so you need to figure it out. I don't remember off the top of me head but day zero was something like a Tuesday so you subtract 3 (Sunday is the first day of the week) and divide by seven. Now delete the remainder and multiple by 7. Subtract that number for the original and you get a value between 0 and 6 which represents the days of the week. If it's 1 (Sunday) add 1 and if it's 6 subtract 4 to get the next Monday. Super simple. But if you don't understand the 1900 date system and how it differs between Excel and Delphi you might end up one day off. My point is differences in dates in the early 1900's do make a difference.

 

You just have to think Decimal just like you do in Excel. 5:29PM or 17 hours and 29 minutes is 0.72777... Today is 39,813. Today at 5:29 in the evening is 39813.72777... If you haven't' played with it before put some dates in excel and change the format to number to lift the veil. It's a good testing ground before you write your time manipulation codes in your macros. BTW almost all computer systems use this system for dates and date calculations. Beware there are some floating point calculation issues out there that might cause slight discrepancies that can cause a greater than comparison to fail. In those cases you need to do some sensible rounding and other tricks.

 

I like doing date and time stuff and I find all kinds of applications. How about a progress indicator for your macro that monitors the progress and estimates the time of/to completion? I do that all the time. If you ever have a problem please feel free to post it or send me a PM and I'll be glad to help.

[stevecasper]

Wow...

 

Cory, you are friggin' brilliant!

 

I've been playing around for a few minutes now with a DoB averaging macro (found the average DoB between my wife and me; among my wife, myself, and our little girl; and the average between all of my brothers). Don't know when I'd ever need this, but it's fun to play with, and gives the Modify command some practical scope.

 

Though I wasn't actually able to use the Modify D/T command to calculate the average: there is no "Divide" in the Modify D/T, so I had to convert it to decimal and back.

You just have to think Decimal just like you do in Excel. 5:29PM

LOL, I just barely found out about the Excel decimal-time relationship... I'm going to need to start learning how to

think in decimal. I'll definitely take your advice and play with it in Excel... well, I actually don't have Excel, I use

Sun's OpenOffice.org but it's pretty much the same, so it hopefully has the same decimal-time set-up.

 

Thanks a bunch for opening my eyes a bit to the power of this new macro command. As I play with it (if I can find

good, practical reasons to do so) I'll be sure to post here looking for suggestions as I'm bound to hit some brick walls.

[Cory]

Thanks, I'm glad you're seeing the light. For newbs it really isn't very intuitive and it takes a bit of studying but it's well worth it. Although I think some of the implementation of time vars in MEP came up a little short of my expectations in terms of interface they are there and makes doing time manipulations a lot easier in most cases. I think that once you know they're there you will begin to realize applications for them in your macros that you wouldn't have before.

[MacroPolo]

Many years ago, I wrote a macro in MEX that used the Julian date formula. (See: http://en.wikipedia.org/wiki/Julian_day).

 

Julian date formula is based on decimal conversions, similar to what Excel uses.

 

It's nice to see this functionality built-in to the new macro express version.