Date arithmatic

[amonaghan]

I know this has come up before but previous posts have just confused me further. I'm looking for a simple solution to the following:-

Store today's date to variable (date or other type?)

Set new date in future and store to variable (date or other type?)

Calculate difference between the two dates in whole days. I'm not looking for time calculations on whole number of days. This should be easy, but I'm getting very frustrated with the way the Date functions work.

e.g. Today  is 13/05/2017 - VarA

A week from now is 20/05/17 - VarB

VarB - VarA = 7

P.S. I'm a bit upset today as my football team just got relegated to the third tier of Scottish football

[rberq]

I use Macro Express 3, and have had little luck with date manipulation.

Here’s a clever method, if I do say so myself.

Use the Date/Time command in a Repeat loop until the “adjusted” date (based on today) matches the future date.  When the loop ends, variable N1 contains the number of days difference between today and the future date.  

//  
Variable Set String %T2% "12/23/2017"  [future date]
Variable Set Integer %N1% to -1  [initialize as negative 1 to allow for future date = today]
Repeat Until %T1% = %T2%  [repeat until computed date = future date]
  Variable Modify Integer: Inc (%N1%)  [increment N1 by 1 each time through loop]
  Date/Time: Save "M/d/yyyy" into %T1%
Repeat End
Text Box Display: %N1%  [number of days between current and future date]
//   

This is what the Date/Time command should look like:  

 

[rberq]

P.S.  Understand that your football and our football are two different things, and different levels of violence.

 

[Cory]

Why isn't subtracting the values working for you?

[Cory]

Is this what you want? 

TimeDifference.mex

[amonaghan]

Thanks, it looks easy like that when prompting for Date only rather than current Date/Time which doesn't seem to give the option of Date only.

Simple macro with displayed results below:-

<DATE/TIME Flags="\xB0" Date="13/05/2017 14:00: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="%dCurrent%" IsDateVar="TRUE" _COMMENT="   Store Current Date"/>
<DATE/TIME Flags="\x93" Date="13/05/2017 16:03:21" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Prompt="Enter date of stay" Left="Center" Top="Center" Monitor="0" Variable="%dDate%" IsDateVar="TRUE" _COMMENT="   Enter Future Date"/>
<VARIABLE MODIFY DATE/TIME DateVar="%dDateDiff%" Option="\x01" LeftVar="%dDate%" RightVal="%dCurrent%" UseInteger="TRUE" MathOpt="\x00" _COMMENT="   Difference = Future Date - Current Date"/>
<TEXT BOX DISPLAY Title="Date Calculation" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Current Date: %dCurrent%\r\n\\par End Date: \\f1 %dDate%\r\n\\par \\f0 Difference: \\f1 %dDateDiff%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

As you can see, I get a decimal part which is undesirable for what I want.

I need the result here to be 7 exactly. Obviously rounding won't do it here.

 

 <--- Second image not required, but I can't work out how to delete it from my post.

[rberq]

The script I gave you in a previous post was for Macro Express Version 3.  The one below is almost identical, but for ME Pro.  The problem of fractional days is avoided, by converting the current-adjusted-date to a text string of format m/d/yyyy before comparing it to the future date.  Again, variable %N[1]% contains the number of days difference when the macro ends.  There is probably another way to cope with the fractional days, but this method works fine for small date differences.  It may take an unacceptably long time to process if you are dealing with dates that are years apart.  

Variable Set String %T[2]% to "5/24/2017"  [future date]
Variable Set Integer %N[1]% to -1
Repeat Until %T[1]% Equals "%T[2]%"
  Variable Modify Integer %N[1]%: Increment
  Date/Time: Set %DT[1]% to an adjusted date/time  [adjust current date forward by N[1] days]
  Variable Modify Date/Time: Convert to Text String  [convert adjusted date to m/d/yyyy format]
End Repeat
Text Box Display: %N[1]%   ....   %T[1]%

 

 

[Cory]

Variable Modify Decimal > Options > Truncate to Integer. Then add 1. 

[Cory]

Here. 

TimeDifference2.mex

[amonaghan]

Thanks everyone for the help. It gave me a few thing to think about. I even tried using VBScript to no avail.

I eventually got this to work, although it requires 3 different variable types for each date.

<DATE/TIME Flags="\xB0" Date="15/05/2017 20:28:11" 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="%CurrentDate%" IsDateVar="TRUE"/>
<CONVERT DATE/TIME TO DECIMAL Source="%CurrentDate%" Dest="%dCurrentDate%"/>
<VARIABLE MODIFY DECIMAL Option="\x06" Destination="%dCurrentDate%" Variable="%nCurrentDate%"/>
<DATE/TIME Flags="\x93" Date="15/05/2017 20:35:05" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Prompt="Enter Target Date" Left="Center" Top="Center" Monitor="0" Variable="%TargetDate%" IsDateVar="TRUE"/>
<CONVERT DATE/TIME TO DECIMAL Source="%TargetDate%" Dest="%dTargetDate%"/>
<VARIABLE MODIFY DECIMAL Option="\x06" Destination="%dTargetDate%" Variable="%nTargetDate%"/>
<VARIABLE MODIFY INTEGER Option="\x01" Destination="%nDateDiff%" Value1="%nTargetDate%" Value2="%nCurrentDate%"/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 %CurrentDate%\r\n\\par %dCurrentDate%\r\n\\par %nCurrentDate%\r\n\\par %TargetDate%\r\n\\par %TargetDate%\r\n\\par %nTargetDate%\r\n\\par \r\n\\par \\f1 Diff = %nDateDiff%\\f0 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

Date/Time: Set %CurrentDate% to the current date/time
Convert Date/Time to Decimal: %CurrentDate% => %dCurrentDate%
Variable Modify Decimal %dCurrentDate%: Truncate to Integer (%nCurrentDate%)
Date/Time: Set %TargetDate% to a user prompted date/time
Convert Date/Time to Decimal: %TargetDate% => %dTargetDate%
Variable Modify Decimal %dTargetDate%: Truncate to Integer (%nTargetDate%)
Variable Modify Integer: %nDateDiff% = %nTargetDate% - %nCurrentDate%
Text Box Display: 

Output

A bit of a sledgehammer to crack a nut really, but it seems to work.

[amonaghan]

I got the second last variable wrong in the DG. Output should be this:-

 

 

[rberq]

Not really a sledgehammer, just working intelligently with the tools the scripting language makes available to you. 

And look how well you understand Macro Express's date abilities now.  Next thing to do is put this code into a separate macro that you can run (call) from other macros, rather than have to recreate the process every time you want to use it. 

[Cory]

What was wrong with my solution? I even wrote you sample macros.

[amonaghan]

Nothing wrong with your solutions Cory and I really do appreciate the help as it kick started my brain. The only difference was I needed it to take today's date automatically, rather than hard coding it or selecting it from the date picker.

[Cory]

I'm glad you got it to work. I hope your team wins the next game.