m204prgmr Posted January 23, 2005 Report Share Posted January 23, 2005 Real simple and it seems almost too good to be true. Is the following analysis correct? Is ME looking at both Date and Time in each varialbe? I have two examples. One ending date that is the current date and one ending date that is the next day. <TVAR2:01:01:1/23/2005 7:52:40 AM><TVAR2:02:01:1/22/2005 7:48:07 PM><IFVAR2:4:01:4:T2><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date is greater than the starting date><ENDIF><TVAR2:01:01:1/22/2005 7:52:40 PM><TVAR2:02:01:1/22/2005 7:48:07 PM><IFVAR2:4:01:4:T2><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date is greater than the starting date><ENDIF> Paul, Regarding your reply (Jan 10 2005, 06:12 PM ) to my SQL post, I created a view on the server then through Access created a Data Access Page and mess with OSQL. This is so much faster for me by not having to go to the server with ME. The HTML page resides on the PC with everything else. I did enjoy learning a little about OSQL, thanks. Regards Robert Quote Link to comment Share on other sites More sharing options...
randallc Posted January 23, 2005 Report Share Posted January 23, 2005 Hey, I like it, anyway! It can get some wrong, but format would fix it. ME looks at it as astring, left to right, so; 1. 1/22/2005 7:52:40 AM, 1/22/2005 7:48:07 PM wrong (Need AM before hour) 2. 12/22/2004 7:48:07 PM, 1/22/2005 7:52:40 AM wrong ("2" of 12 month>"/" slash) etc 3. Format yyyy/MM/dd AMPM hh:mm:ss would fix it 4. 2004/12/22 PM 07:48:07 , 2005/01/22 AM 07:52:40 OK! Best, Randall <DT:yyyy/MM/dd AMPM hh:mm:ss T:01:1:><DT:MM/dd/yyyy hh:mm:ss AMPM T:03:1:><IDELAY:2><DT:yyyy/MM/dd AMPM hh:mm:ss T:02:1:><DT:MM/dd/yyyy hh:mm:ss AMPM T:04:1:><IFVAR2:4:02:4:T1><TBOX4:T:1:000373Center000433000200:000:Tell me that the ending date (T2) is greater than the starting date %T1%=T1 ........(ie %T3%(T3)) %T2%=T2 ........(ie %T4%(T4))><ELSE><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date is NOT greater than the starting date %T1%=T1 %T2%=T2><ENDIF><TVAR2:01:01:1/22/2005 7:52:40 AM ><TVAR2:02:01:1/22/2005 7:48:07 PM ><IFVAR2:4:02:4:T1><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date (T2) is greater than the starting date %T1%=T1 %T2%=T2><ELSE><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date (T2) is NOT greater than the starting date %T1%=T1 %T2%=T2><ENDIF><TVAR2:01:01:2005/01/22 AM 07:52:40 ><TVAR2:02:01:2005/01/22 PM 07:48:07 ><IFVAR2:4:02:4:T1><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date (T2) is greater than the starting date %T1%=T1 %T2%=T2><ELSE><TBOX4:T:1:CenterCenter000278000200:000:Tell me that the ending date (T2) is NOT greater than the starting date %T1%=T1 %T2%=T2><ENDIF> Quote Link to comment Share on other sites More sharing options...
m204prgmr Posted January 26, 2005 Author Report Share Posted January 26, 2005 Randall, My apologies for not getting back to you sooner. Thank you. Your suggestions are working. What would I need to do to do a time difference and if necessary, do a date and time difference? I'd like to calculate the minutes before putting this into Excel. Unless someone knows a trick. I've tried appending comma delimitted to Excel from ME where there is a formula in place in Excel. The append jumps to the next free line instead of the next line in succession. Robert Quote Link to comment Share on other sites More sharing options...
randallc Posted January 27, 2005 Report Share Posted January 27, 2005 Hi, See another link; those indices might help, but I had only thought about a 6 hour gap, so did not have to take month dates into account. My Webpage PGMacro utility program is your best bet, I think, as there's an inbuilt time diffference function. (I haven't used it, though!) Wouldn't this be easier in excel itself with vba macro or formula? not that I'd know! Best, Randall Quote Link to comment Share on other sites More sharing options...
randallc Posted February 6, 2005 Report Share Posted February 6, 2005 Hi, Robert, I think I have a macro working which you could modify to do date/ time differences; let me know if it helps. Date / Time Differences Calculates these up to 60years back, down to milliseconds if set in approprate format To set your own , format "yyyymmddhhmmsszzz" from text/ Date/Time input to string [Feb9th; now uses usual Windows format]; "m/d/yyyy h:m:s AMPM" or "mm/dd/yyyy hh:mm:ss", but watch your Regional Date settings! This macro prompts for number of days/ years back etc,and checks against current date/ time. It can be used to input your own start and end date/ time in %T1% and %T2% instead of prompting for days back. It works by calculating number of months to go back, then asking for date of last day of last month and adding it to date difference, looping through for date of end previous month etc... and adding, till it reaches month needed . The PGM Function library is NOTneeded, but can be selected to check after macro runs (only checks date calculation, not time! (Only use PGM Function library part if installed) Best, Randall Edited Feb 9th 05 PGM checker missing a line to tale D9 into N17; I have swapped the macro. Also input now more easily as usual Windows format, and results into Environment Vars and Registry entries for llater retrieval without preserving ME integers, etc. Edit Apr27th05 - This macro crashing out?; I have re-uploaded it. Please tell me if problems? Randall DateTimeDifferences.mxe Quote Link to comment Share on other sites More sharing options...
joe Posted February 7, 2005 Report Share Posted February 7, 2005 Hello Randall! Geez ... we thought everybody needed or PGM Functions Library Since downloading this playable, and giving it a run-through, I would like to spend more time examining it. It looks interesting. As to the { DateTime } functions, it is true that they do not yet do anything with time-of-day calculations. Here is a quote from the user's manual that comes with the PGM Functions Library: The functions are all based on Julian Day numbers, which are not to be confused with Julian Dates, whose calendar ended on October 15th, 1582 when the Gregorian calendar replaced it. The Gregorian calendar is the reformed Julian calendar with the year fixed at 365 days except for leap years which contain 366 days. Leap years are years that are exactly divisible by 4 except for century years which must also be divisible by 400. This is the calendar in common use today. It may be interesting to note that a Julian Day number starts at noon and not midnight as one would expect. ... okay, enough trivia! Quote Link to comment Share on other sites More sharing options...
randallc Posted February 8, 2005 Report Share Posted February 8, 2005 Hi, joe, Thanks for your interest! I should have commented if you are looking at the loop; N18 starts with current date, and (taking it off to find how many days back for date of last day of last month) keeps adding these to get the number to go back for previous last day of month! N17 starts with current date difference (pos or negative) to keep adding the last day number of the month, and finally give the date difference in days. The calculations before and after the loop otherwise are just boring hour/ date/ etc calcs. Best, Randall PS I have actually edited the macro; the PGM check part only, and have re-loaded it above on the list (it had lost one line; its retrieval of its own calc). This does not affect the basic mE3 part. Quote Link to comment Share on other sites More sharing options...
m204prgmr Posted February 8, 2005 Author Report Share Posted February 8, 2005 Randall, You amaze me...this looks pretty slick, thank you. My format on an HTML page that I am copying is 2/7/2005 8:41:40 AM for the start time and 2/7/2005 8:44:53 AM for the ending time. I assume I should modify MY format to fit your format and formula of yyyymmddhhmmsszzz? My information is from a SQL Server. I ran a script and created a View in SQL then through Access I was able to create an HTML page showing me some basic information. SQL is not a strong point of mine..... Robert.. Quote Link to comment Share on other sites More sharing options...
randallc Posted February 8, 2005 Report Share Posted February 8, 2005 Hi, Robert, Thanks! Yes, though I can't picture how you're using the mE3 macro with all the rest! You could, theoretically, retrieve all the parts of your current format as strings, change to numbers, and substitute them in the appropriate integers etc in my macro; it depends how easy that would be. I have retrieved from the format I use the year, date , etc from the string. (using position in string, so I found things were always the same place; in your current format, you might need to find "\", ":" etc if some items are in varying places in the string (double figure/ single figure times, etc). I only really use the format to evaluate T5 and T6 , T7 and T8, and an "If" "And" for year, date, day would work in those "If" statements just as well (see the Rem statements). If you are using VBA or VBS in SQL or Access, you could use a date/time difference function anyway, I imagine? (not that I have looked into it!) I have actually edited the macro; the PGM check part only, and have re-loaded it above on the list (it had lost one line; its retrieval of its own calc). [Edited Feb 9th 05 PGM checker missing a line to tale D9 into N17; I have swapped the macro. Also input now more easily as usual Windows format, and results into Environment Vars and Registry entries for llater retrieval without preserving ME integers, etc. ] Best, Randall Quote Link to comment Share on other sites More sharing options...
randallc Posted February 8, 2005 Report Share Posted February 8, 2005 Robert, Again! Alternatively, if you can put the dates and times into strings T2 and T1: this would convert them to my format in ME3: uses MacroRuninVariable to put the date/time (with correct format) in Text/Date/Time formatter; Macro Express Script for "datadate1" (Alt+Shift+D) 8/02/2005 4:21 PM Variable Set String %T2% "2/7/2005 8:41:40 AM" Variable Modify String: Trim %T2% Replace " " with "\s" in %T2% Variable Modify String: Copy %T2% to %T99% Variable Set String %T1% "2/7/2005 8:44:53 AM" Variable Modify String: Trim %T1% Replace " " with "\s" in %T1% Variable Modify String: Copy %T1% to %T98% Text Box Display: Variable Set String %T34% "<DT:yyyymmddhhmmsszzz\sT:02:2:%T99%>" Variable Set String %T36% "<DT:yyyymmddhhmmsszzz\sT:01:2:%T98%>" Run Macro in Variable %T34% Run Macro in Variable %T36% Text Box Display: <TVAR2:02:01:2/7/2005 8:41:40 AM><TMVAR2:01:02:00:000:000:><TMVAR2:21:02:00:000:000: \s><TMVAR2:09:99:02:000:000:><TVAR2:01:01:2/7/2005 8:44:53 AM><TMVAR2:01:01:00:000:000:><TMVAR2:21:01:00:000:000: \s><TMVAR2:09:98:01:000:000:><TBOX4:T:1:CenterCenter000278000200:000:T99=%T99% T98=%T98% ><TVAR2:34:01:<DT:yyyymmddhhmmsszzz\sT:02:2:%T99%>><TVAR2:36:01:<DT:yyyymmddhhmmsszzz\sT:01:2:%T98%>><RUNMACVAR:34><RUNMACVAR:36><TBOX4:T:1:CenterCenter000278000200:000:T99=%T99% T98=%T98% T2=%T2% T1=%T1%> [Edited Feb 9th 05 I have swapped the macro. Also input now more easily as usual Windows format (as above), and results into Environment Vars and Registry entries for later retrieval without preserving ME integers, etc. ] Best, Randall Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.