Date/Time Calculations

[stevecasper]

Ok, so I've finally started playing with Date/Time calculations in a practical way, and I've run into a road-block.

 

Situation:

* Accounts may need to be reviewed every day, but new notes regarding the review only need to be made once every 14 days.

* When the account loads, I can see, at a glance, whether or not it is going to need new notes by looking at the date. This is very simple. However, the application the account is loading into (an Access database) often runs PAINFULLY slowly. If new notes are not needed, I can fill in a few boxes and then move on to the next account.

* I want to be able to have Macro Express "read" the date of the last note and compare that date to the current date. If the difference is less than 14 days, I want the macro to be able to fill in the necessary boxes without any intervention from me.

 

The Problem:

Though I can make the macro highlight the "previous" date, copy it, and save it to a Text Variable, I can't seem to find any way to convert it to a Date/Time variable. I'm probably going about it the wrong way, but it doesn't look like I can save the clipboard as a D/T variable.

 

Any pointers on how to do this? Is it even possible with the current build of MEP?

 

Thanks!

[Cory]

Steve this is a huge soapbox for me and you may have even heard me complain about this before and I did a fair amount of complaining in the beta forum. Simply speaking you can't go from text to date variable. There are several things like this and I think ISS realizes these shortcoming and may be addressing this shortcoming soon. As it is I have 3 major applications I am deferring until the last minute in the hope that the fix will be in soon.

 

If you need to write this now you must do some parsing math. Some solutions can be simple as long as they don't have to be perfect but if they have to be perfect for all times sake it's a mess. A long time ago I wrote a macro to calculate the day of the year which can be useful but of course you need to do something different for the first 2 weeks of January in your case. If you would like to work on a short term solution you could pirate this or I could help you with something even more simple. For instance if it can occasionally be off a day or two it gets much simpler.

 

I recently did some research and came up with a mathematical means of creating the serial date value used by MEP, Excel and the like. Read about serial time here. I now have an algorithm to make my own macro to be used as a subroutine to calculate serial date and time which as a decimal value which you can then get into a time var for comparison. I spent a lot of time working all the leap year rules and all that out so if you're really in need I might be persuaded to actually code it.

[redcordial]

Hi Scasper, I have recently been working on a database of greyhound race results which are stored in a text file. I needed to calculate the start and end dates for evaluating

a particular set of results. This macro counts back the number of days from today's date. I have set the variable LastNoteDate for the demo.

The date format can be changed in the date/time variable if required.

Hope this helps.

 

 

<VARIABLE SET STRING Option="\x00" Destination="%LastNoteDate%" Value="01 Mar 2009"/>
<VARIABLE SET INTEGER Option="\x00" Destination="%DayCounter%" Value="0"/>
<REPEAT UNTIL Variable="%TodaysDateAdjusted%" Condition="\x00" Value="%LastNoteDate%"/>
<VARIABLE MODIFY INTEGER Option="\x08" Destination="%DayCounter%"/>
<DATE/TIME Format="dd mmm yyyy" Flags="\xB2" Date="30/Dec/99" Day_Offset="%DayCounter%" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%TodaysDateAdjusted%" IsDateVar="FALSE"/>
<END REPEAT/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 LastNoteDate \\f1 %LastNoteDate%\r\n\\par \\f0 Days Since Last Note \\f1 %DayCounter%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

[stevecasper]

Thanks guys. Since I started this thread, new procedures descended from the powers that be, checking the date is no longer sufficient... now we have to check the date and the contents of the previous note (which can't be automated, obviously). This will still result in over 99% of accounts being passed on till the 14 days have elapsed, but sadly I know have to actually use my eyes before passing.

 

I'm going to still play with the suggestions you both made and see what the best way for me to accomplish the date-difference task will be and I wanted to thank you for the responses.