Dates

[cyberchief]

Ok... this is a little hard to follow... I am just beginning to research this problem. I thought I would throw this out there for the general populace to hash over and see if they come up with anything.

 

I am creating an order that must have a specific due date depending on a couple variable dates. The order is to combine 2 accounts. Each account has a bill date. I need to write an order. The due date of the order cannot be 3 days prior to either account's bill date nor can it be within 5 days of after either account's bill date. Using today's date out 2 (2 days from today), I need to compare that date and see if it falls within either of the 2 accounts billing cycle (the 3 days prior and 5 days after). Both accounts may have different billing cycle dates.

 

Example1:

 

Account1 has a billing date of May 15th. Therefore, May 12th through May 20th is out of the question.

 

Account2 has a billing date of May 1st. Therefore, April 28th through May 6th is out of the question.

 

Todays date is May 4th. 2 days out from today is the 6th, so I cannot use that. I would need the variable to chose the next available date which would be May 7th.

 

Example 2:

 

Account1 has a billing date of May 15th. Therefore, May 12th through May 20th is out of the question.

 

Account2 has a billing date of May 9th. Therefore, May 6th through May 14thth is out of the question.

 

Since bill dates are closer together... I cannot use May 6th through May 20th.

 

Todays date is May 4th. 2 days out from today is the 6th, so I cannot use that. I would need the variable to chose the next available date which would be 21st.

 

Am I SOL?

[kevin]

Cyberchief,

 

Have you looked into the 'Date Math' functions that are part of the PGM Functions Library? See www.macros.com/products.htm and www.pgmacros.com/pgm_functions.htm.

[randallc]

Hi Kevin,

It would be useful to see some pgmMacro example scripts like this situation, to see how they are used in context?

Best, randall

[randallc]

Clearly it would be best to use PGmacros if possible; perhaps Kevin has some ideas; using my macros may work but hard to follow, reproduce or fix!

Here's the logic, though!

// Date calculator (AVOID CERTAIN DATES); needs date set as "yyyyMMddhhnnsszzz", though for dates "yyyyMMdd000000000"

Repeat Start (Repeat 2 times)

  Date/Time: Save "dd/MM/yyyy" into %T21%

  // Set first account date T21 , calculator string T1/ Set today's date in calculator string T2

  Variable Modify String: Append "\s00:00:00" to %T21%

  Variable Set String %T34% "<DT:yyyymmdd000000000\sT:01:2:%T21%>"

  Run Macro in Variable %T34%

  Date/Time: Save "yyyymmdd000000000" into %T2%

  // Set from T1 a string T50 with the first 9 non-acceptable dates appended,

  Variable Set String %T31% "<VARSR:01><

  Run Macro in Variable %T31%

  Variable Set String %T13% from Environment Variable

  Variable Modify String: Convert %T13% to integer %N1%

  If Variable %T1% < variable %T2%

    Variable Modify Integer: %N1% = 0 - %N1%

  End If

  // (Repeat loop to generate 9 dates, using N1 counter 9 times, starting at "-3" days pastT1 in date/text format

  Variable Modify Integer: %N1% = %N1% - 3

  Repeat Start (Repeat 9 times)

    Date/Time: Save "yyyymmdd" into %T1%

    Variable Modify String: Append "|%T1%" to %T50%

  Repeat End

Repeat End

// Repeat add 1 date (starting N3(=2) +2 days future to  T2 to check if in T50; until it is your answer

Repeat Until %T50% Does not contain %T2%

  Date/Time: Save "yyyymmdd" into %T2%

  Date/Time: Save "dddd, d MMMM yyyy" into %T3%

  Variable Modify Integer: Inc (%N3%)

Repeat End

Text Box Display: Answer

Best, Randall cf DatebillGap2.exe

EDIT; changed file; see my other posts in this thread

DatebillGap3.mxe

Edited May 9, 2005 by randallc

[cyberchief]

Randall,

 

Thanks. And you are correct. Your Macros are hard to follow. Haven't got to reproducing or fixing them yet. still working on following. Thanks a bunch. I am going to look at yours and see what I can come up with. If that don't work, i will look at the library.

[floyd]

Cyberchief -

Here is your solution using Julian Date functions from the PGM Functions Library:

// ASSUMPTIONS:
// Today is May-04-2005
// Account #1 has a billing date of May-15-2005
// Account #2 has a billing date of May-09-2005

// DEFINED VARIABLES:
// D1 = Today's date
// D2 = Bill date for account #1
// D3 = First bad date for account #1 (bill date - 3)
// D4 = Last bad date for account #1 (bill date + 5)
// D5 = Bill date for account #2
// D6 = First bad date for account #2 (bill date - 3)
// D7 = Last bad date for account #2 (bill date + 5)
// D8 = The correct date (the answer)

// Save today's date to a variable (D1) and then convert it to a Julian day.
Variable Set String %T1% "20050504"
Write Registry String: "ParameterString1"
Macro Run: { DateTime - Date to Julian }
Read Registry Decimal: "ReturnDecimal1"

// Convert the billing date to a Julian day (D2) for account #1.
Variable Set String %T1% "20050515"
Write Registry String: "ParameterString1"
Macro Run: { DateTime - Date to Julian }
Read Registry Decimal: "ReturnDecimal1"

// Subtract 3 to get the first bad date within the billing range and then add 5 to get the last
// bad date.
Variable Modify Decimal: %D3% = %D2% - 3
Variable Modify Decimal: %D4% = %D2% + 5

// Convert the billing date to a Julian day (D5) for account #2.
Variable Set String %T1% "20050509"
Write Registry String: "ParameterString1"
Macro Run: { DateTime - Date to Julian }
Read Registry Decimal: "ReturnDecimal1"

// Subtract 3 to get the first bad date within the billing range and then add 5 to get the last
// bad date.
Variable Modify Decimal: %D6% = %D5% - 3
Variable Modify Decimal: %D7% = %D5% + 5

// Copy today's variable (D1) to the answer variable (D8) then add 2 days to it before starting
// the loop.
Variable Modify Decimal: Copy %D1% to %D8%
Variable Modify Decimal: %D8% = %D8% + 2

// Loop by incrementing the answer variable by 1 each time until we find a good date. Check if
// the variable falls within either date range. If it does then we need to increment, otherwise
// we have the answer so exit.
Repeat Until %N1% <> %N1%
 If Variable %D8% >= variable %D3%
   AND
 If Variable %D8% <= variable %D4%
   Variable Modify Decimal: %D8% = %D8% + 1
 Else
   If Variable %D8% >= variable %D6%
     AND
   If Variable %D8% <= variable %D7%
     Variable Modify Decimal: %D8% = %D8% + 1
   Else
     Repeat Exit
   End If
 End If
Repeat End

// Convert the (D8) Julian day answer variable to a date string (T2).
Write Registry Decimal: "ParameterDecimal1"
Macro Run: { DateTime - Julian to Date }
Read Registry String: "ReturnString1"

Macro Return

The example should be easy to follow. However, you must have a copy of the PGM Functions Library in order to run it. A 30-day trial can be downloaded from PGM Functions Library and Docs.

 

Hope this helps.

JulianDate.mxe

[randallc]

Thanks, Floyd!

I can use another PGMacro library function to set the date format, though it does not like international date formats.

(attached below)

Write Registry String: "ParameterString1"

Macro Run: { DateTime - Format Input String }

Read Registry String: "ReturnString1"

<REGWSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ParameterString1><MACRUN2:{ DateTime - Format Input String } ><REGRSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1>

(Nicer than ;

Variable Set String %T34% "<DT:yyyymmdd000000000\sT:01:2:\x25BillDate(%N3%)\x25\s00:00:00>"

  Run Macro in Variable %T34%

<TVAR2:34:01:<DT:yyyymmdd000000000\sT:01:2:\x25BillDate(%N3%)\x25\s00:00:00> ><RUNMACVAR:34>

I have also set a check for date format in the input function of my previous macros which do not use this.

<REGRSTR:97:HKEY_CURRENT_USER\Control Panel\International\sLongDate>

DatebillGap3.mxe

(May need)

DateDifferencesInput.mxe

I can also use PGMacro library functions in my previous algorithm, if I like to avoid the “if/else/and” decimal components.

JulianDate3.mxe

They may make more sense than my algorithm (which actually only needs 1 date formula to calculate date differences (by Julian often otherwise) interestingly!

DatebillGap4.mxe

Best, Randall

The “Date Differences” in PGMacros is from the "{ DateTime - Span of Days }” macro set;

Variable Set String %T1% "{ DateTime - Span of Days }, %T2%, %T1%"

  Write Registry String: "PgmFunction"

  Macro Run: { PGM Function }

  Read Registry Integer: "ReturnInteger1"

(Nicer than running my large variable macro; or running “DateDifferencesInput” as a separate macro)

<TVAR2:01:01:{ DateTime - Span of Days }, %T2%, %T1% ><REGWSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Pgm Functions\PgmFunction><MACRUN2:{ PGM Function } ><REGRINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1>

JulianDateOrg.mxe

[cyberchief]

Thanks Floyd. I will talk to our operations manager about this. I am not sure if they will be willing to fork out a little more cash for this project... we will be looking at purchasing more licenses for ME for about 3 more terminals. We would have to purchase the Library for all 4 or 5 terminals that will be running. This sounds like a great solution, though. I will do what I can.

[cyberchief]

Floyd,

 

Question for you. The Library with source code is more expensive... is the library a specific software upgrade or is it just pre-written macros? My thinking is if we purchased the library with source code... can I view the source code and use that to type more macros in ME on other terminals? Or do I have to purchase the library for every terminal using ME? I know financially you would want us to purchase it for all terminals... but I don't know that I could get that done. And we would prefer all terminals to be set up the same. It could make the difference whether we purchase the source code version, or just make do with what we have. Thanks!

[floyd]

Cyberchief -

 

The only difference between the source and non-source code PGM Functions Library is that with the former, you can view the code for all but the PGM System and Variable Management functions. Both libraries have the same number of macro functions and both do the same exact thing.

 

The source-code version was never meant to be multi-licensed. Its only purpose was to allow interested parties to see some very nice Macro Express coding and perhaps to use some style of it in their own macros. If you purchase multi-licensed non-source code versions, Joe may be more than happy to give you a source code version.

 

The PGM Functions Library is licensed in the same manner as Macro Express, which is on a per seat (machine) basis. One machine = one license. The more licenses you buy the less it costs per machine. For example, a single user PGM Functions Library license is normally $29.95, a 10-seat license is $20.90 per seat, a 20 seat license is $16.36 per seat, 100 seats is $8.29 per seat, and so forth.

 

Email joe@pgmacros.com directly to discuss pricing ... he can help.

 

The PGM Functions Library is just that. A library of ready-made functions that begins where Macro Express leaves off. You may not need or ever use all of them, just like we will never use all of the commands within Macro Express. You can pick and choose which functions to use and place in your own macro library (.mex) files so that you can keep "all machines the same" as you mentioned.

[randallc]

Hi,

I have the Dates macro working using the "Wizard", though have not posted the latest version, (this script does not work with old versions -only posted as example of command syntax**) as there have been problems and I can't get enough feedback to fix it!

It can do your calculations on dates in Excel sheet, and post a result date, without opening Excel; i think this is the way to go for converting my Word macros when I get it running.

Let me know if you want to try the Excel commands; they can be converted to not need the "MEBasic" (which I use for convenience), but would always need the "Wizard" running in the background.

I would like to know if it works with USA dates too?

Best, Randall

ExcelDates.JPG

Repeat Start (Repeat 5 times)

  // #Region --- MECodeWizard generated code Start ---Excel Cell Read  --->Wizard MEBasic

  Variable Set String %T1% "ExcelReadCell$["C:\winword\Excel\Book1.xls","1","E",%N1%,"0",0,0]"

  Macro Run: {_Basic ME Lite1}

  Variable Set String %T1% "%AnswerString%"

  Variable Modify String: Save %T1% to Environment Variable

  // #Region --- MECodeWizard generated code Start ---Excel Cell Read  --->Wizard MEBasic

  Variable Set String %T1% "ExcelReadCell$["C:\winword\Excel\Book1.xls","1","E",(%N1%+1),"0",0,0]"

  Macro Run: {_Basic ME Lite1}

  Variable Set String %T2% "%AnswerString%"

  Variable Modify String: Save %T2% to Environment Variable

  // Run Date Calculator, avoid 9 days in 2 dates, starting checking from today; return to T3

  Macro Run: BasicJulianMYPGM

  // #Region --- MECodeWizard generated code Start ---Excel Cell Write--->Wizard MEBasic

  Variable Set String %T1% "ExcelToCell$["c:\winword\excel\Book1.xls","1","F",%N1%,"0","%AnswerString%",0]"

  Macro Run: {_Basic ME Lite1}

Repeat End

// SAVE at the end (This opens as well, though not necessary)

Variable Set String %T1% "ExcelReadCell$["C:\winword\Excel\Book1.xls","1","E","1","Save",1,1]"

Macro Run: {_Basic ME Lite1}

Macro Return

Best, Randall

[cyberchief]

Thanks Randall. I currently have a macro set up to do the date conversions... This has been working for me good so far. If I have time, I will take a look at this example. Thanks again!