Help with date and time

[UAMike]

I do not have much experience using dates with MEP. I was a bit confused when looking over the documentation today.

 

I am trying to create a macro that alerts me if a certain date is within the last 90 days. To accomplish this, I need to highlight some text inside a program (ex: 12/01/2015) and compare it to today's date. I'm not exactly sure what combination of date function(s), variables, clipboard functions I need to get this to work.

 

Any advice?

 

Thanks in advance.

[Cory]

The most important thing to know about dates is that they're internally stored as decimal values. 1 = 1/1/1990. And each date is a unit value of one. Time within a day is a decimal amount. EG. 0.5 = noon. So really all you'e wanting to do is set a date value and modify it to remove 90 days. Then compare that date to your date to determine if it's less than it. So create a variable called DateThreshold or something like that. Subtract 90 days from it. Now compare that to the suspect date..

[UAMike]

The most important thing to know about dates is that they're internally stored as decimal values. 1 = 1/1/1990. And each date is a unit value of one. Time within a day is a decimal amount. EG. 0.5 = noon. So really all you'e wanting to do is set a date value and modify it to remove 90 days. Then compare that date to your date to determine if it's less than it. So create a variable called DateThreshold or something like that. Subtract 90 days from it. Now compare that to the suspect date..

I am encountering difficulty using my clipboard contents to set a date value. It will not allow me to do this because my clipboard value is recognized as text.

[Cory]

Correct. You need to cast data from one type to another. Many programming environments do this really well and without user intervention. Internally and invisibly they parse the string and calculate the value based on those. And they do a good job of this over a wide variety of date formats. But often in MEP you need to help it out or parse it yourself. Post and example of your date and I'll try to do a quick example for you.

[UAMike]

I am copying for example a date of 01/01/2016. The dates I will be using will not vary from this MM/DD/YYYY format.

[Cory]

Attached is a sample macro file for you. Below is the text for cursory reference. You should be able to at least understand the approach here. Also good to save this as a macro you can call later in other applications. Here I snab the text values for day, month, and year and convert them. Then tweak them and add them to the default value of a DateTime variable. Before I used this method I tried to to do the math but it's hell once you start considering all the leap year rules and so forth. I did it but it was very complicated. In VB.NET this is a simple cast command. I hope someday ISS will add an option to Variable Modify String to cast to DateTime. Alternatively one could make a simple JavaScript to do this instead.

// Parse out the string values and cast
Text Box Display: Default Date Value // Illustrate the zero date default value
Variable Set String %strDoB% to "08/19/1968" // 'As sample I set the date to my birthday. This might be done with a clipboard instead.
Variable Modify String: Copy a substring in %strDoB%, starting at 4 and 2 characters long to %strDoBDay% // Copy the day string
Variable Modify String %strDoBDay%: Convert to Integer (%intDoBDay%) // Cast the day string to an integer value
Variable Modify Integer: %intDoBDay% = %intDoBDay% + 1 // To align the day in 1900 12/30 plus 1 to be 12/31
Variable Modify String: Copy a substring in %strDoB%, starting at 1 and 2 characters long to %strDoBMonth% // Copy the month string
Variable Modify String %strDoBMonth%: Convert to Integer (%intDoBMonth%) // Cast the month string to an integer value
Variable Modify Integer: %intDoBMonth% = %intDoBMonth% - 1 // To make it zero based
Variable Modify String: Copy a substring in %strDoB%, starting at 7 and 4 characters long to %strDoBYear% // Copy the year string
Variable Modify String %strDoBYear%: Convert to Integer (%intDoBYear%) // Cast the year to an integer value
Variable Modify Integer: %intDoBYear% = %intDoBYear% - 1900 // To align with the 1/1/1900 convention
// Add the sample DoB value
Variable Modify Date/Time: %dtDoB% = %dtDoB% + %intDoBDay% Days // Simply add the day integer
Variable Modify Date/Time: %dtDoB% = %dtDoB% + %intDoBMonth% Months // Simply add the month integer
Variable Modify Date/Time: %dtDoB% = %dtDoB% + %intDoBYear% Years // Simply add the year integer
Text Box Display: Result

CastDate.mex

[UAMike]

Wow. I had a vague idea of how I was going to extract the string values, but the parts where you tweaked the values to align with the default value would have completely escaped me. Many thanks, Cory!

[Cory]

You're welcome. It escaped me originally too!

[terrypin]

I normally use Excel for date-handling sections of macro tasks. That deals with all the formatting transformations, making it so much easier.

[Naresh]

Hi,

i have a  specific date and time , but my requirement is my time needs to be substract 2 hours by using macro express pro

Example : if my date and time is 03/27/2017 11:34:45 PM then my output should be 03/27/2017 09:34:45 PM

as well as

if my date and time is 03/27/2017 01:34:45 AM then my output should be 03/26/2017 11:34:45 PM

in the second scenario it has to subtract the date also.

Please help me out

[terrypin]

Hi Naresh,

This macro should show you how to do it:

Command text

// Note: Your date format is USA, mine is UK. The prompt below should use the correct format automatically, based on your PC settings.
// I've used redundant Text Box Display commands to show results at each stage.
Date/Time: Set %dtDateTime% to a user prompted date/time
Text Box Display: Input entered
Convert Date/Time to Decimal: %dtDateTime% => %dDateTime%
Text Box Display: Input entered in decimal form
Variable Modify Decimal: %dDateTime% = %dDateTime% - 0.0833333333
Text Box Display: Subtracted one twelfth of a day, i.e. 2 hours
Convert Decimal to Date/Time: %dDateTime% => %dtDateTime%
Text Box Display: Result

Code

<COMMENT Value="Note: Your date format is USA, mine is UK. The prompt below should use the correct format automatically, based on your PC settings."/>
<COMMENT Value="I've used redundant Text Box Display commands to show results at each stage."/>
<DATE/TIME Format="mm/dd/yyyy" Flags="\xB3" Date="29/03/2017 10:13:13" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Prompt="Enter the original date and time." Left="Center" Top="Center" Monitor="0" Variable="%dtDateTime%" IsDateVar="TRUE"/>
<TEXT BOX DISPLAY Title="Input entered" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 d\\f1 t\\f0 DateTime\\f1  = \\f0 %d\\f1 t\\f0 DateTime%\r\n\\par \r\n\\par }\r\n" Left="Center" Top="485" Width="278" Height="142" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<CONVERT DATE/TIME TO DECIMAL Source="%dtDateTime%" Dest="%dDateTime%"/>
<TEXT BOX DISPLAY Title="Input entered in decimal form" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 dDateTime\\f1  = \\f0 %dDateTime%\r\n\\par \r\n\\par \r\n\\par }\r\n" Left="Center" Top="485" Width="278" Height="142" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<VARIABLE MODIFY DECIMAL Option="\x01" Destination="%dDateTime%" Value1="%dDateTime%" Value2="0.0833333333"/>
<TEXT BOX DISPLAY Title="Subtracted one twelfth of a day, i.e. 2 hours" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 dDateTime\\f1  = \\f0 %dDateTime%\r\n\\par \r\n\\par \r\n\\par }\r\n" Left="Center" Top="485" Width="278" Height="142" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<CONVERT DECIMAL TO DATE/TIME Source="%dDateTime%" Dest="%dtDateTime%"/>
<TEXT BOX DISPLAY Title="Result" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 dtDateTime\\f1  = \\f0 %dtDateTime%\r\n\\par \r\n\\par \r\n\\par }\r\n" Left="Center" Top="485" Width="278" Height="142" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

 

Macro

Subtract 2 hours.mex

Terry, East Grinstead, UK

[terrypin]

" Please help me out "

Did my work for you, four days ago, do so?

Feedback is always appreciated.

Terry, East Grinstead, UK

 

[Brian White]

On 4/1/2017 at 5:38 AM, terrypin said:

" Please help me out "

Did my work for you, four days ago, do so?

Feedback is always appreciated.

Terry, East Grinstead, UK

 

 

I don't know if this helped him two years ago, but it helped me just now!  Thank you!

[terrypin]

Pleased to hear it Brian.

 

Still waiting to hear from Naresh - from nearly three years ago!