Comparing T30 to T31 and adding 7 days?????

[lace6921]

HELP!!!!

 

I am building a Macro to run in ERP (probably the most complex macro ever written in my world and will change procurement forever). It will create a Sales Order and create a Purchase Order.

 

T30 Sales order Date (ex: 06152008)

T31 Purchase Order Date (ex: 06202008)

 

Where I am running into trouble is when the Sales Order Date (T30) is less than the Purchase Order Date (T31). If the Sales Order Date (T30) is less than the Purchase Order Date (T31) I need the Sales Order date (T30) to calculate the %T31% and add 7 days and then input the date in the system.

 

Example.

T30 Sales Order Date = 06152008

T31 Purchase Order Date = 06202008

 

Basically 06152008 is less than 06202008.

 

So I need the macro to add 7 days and put a new value of 06272008 into the sales order. How do I get the macro to add the 7 days.

 

I have an If variable %T30% <= %T31% statement. But for the life of me cannot figure out how to get Macro Express to add the seven days to the date for the Purchase Order (T31)

 

Can anyone share any insight or wisdom. Suggestions or hints are more than welcome.

 

Thanks

Leslie

[patgenn123]

Hey Leslie!

 

Take a look at "Text" in the commands list. Go to Date/Time and there's your answer.

 

Pat

[kunkel321]

Hey Leslie!

 

Take a look at "Text" in the commands list. Go to Date/Time and there's your answer.

 

Pat

 

Ar you sure that will work Pat?

Iv'e rattled by brain over this very topic in the past.

I've only ever been able to get a date offset when starting from "today."

 

As a simplified example, see if you can write one to 'Promt User for Date,' then add 7 days to the given date and print it into Notepad....

=====

 

THis seems like a pretty basic function, so it may well be possible.....

[kunkel321]

Intrigued by my own challenge, I started tinkering...

 

THe following:

<DT:ddMMYYYYT:03:4:CenterCenterEnter DateTF><TMVAR2:05:03:03:000:000:><NMVAR:01:03:1:0000003:2:0000007><TEXTTYPE:N3 = %N3%.>

prompts for a date, then converts it to an integer, then adds 7 to the integer, then prints the answer into a text field (if one is available).

 

I figure that at the point it is converted to an integer, MacEx 'forgets' that it's supposed to be a date.

So if it starts as (ddMMYYYY) 06152008 and you add 7 you just get 06152015.

 

In the Date/Time function (not the old "Date" function) there is an option to "use specific time/date" but it can't be defined dynamically, nor can you add to it. It's unfortunate that MacEx can't use a method similar to Palm or Excel. It simply stores dates as the number of days since the Big Bang. So today is 39614 days from the Big Bang (with I think is 1/1/1900 in Excel-speak). Another option that would be nice is if there was support for actual date variables such as D1, D2, ...., D99.

 

Other thoughts???

-steve

[rberq]

I had never looked for date routines in ME; I'm surprised there isn't more built in.

 

First, be aware that your IF T30 <= T31 often won't work right. For example, December 12, 2008 will appear to be later than, say, June 10, 2009, or for that matter later than ANY date up to December 11 of ANY year. You need to use Variable Modify String | Copy Part of String, to store your dates in two new variables in the format YYYYMMDD rather than MMDDYYYY. Then you can legitimately do a less than or equal comparison.

 

From there, the only way I know to add to the date is by the brute force method, which is too long to go into fully here. Basically, convert the DD part of your date to an integer, add 7, check whether you have exceeded the possible number of days in the MM month (some months are 30 days, some 31, and don't forget leap year Februaries are 29 except sometimes when the leap year is a century turnover). Then bump month up by one if you went past the maximum days, and reduce DD days by length of the month just past, then do the same check for year in case you rolled the month from 12 to 13. It's a real pain -- maybe if you google "MacroExpress date conversion" you will find where someone has done it. It seems odd that, since ME allows you to go forward and back from current date, they didn't allow you to easily specify your own initial date.

 

Here's another possibility to try: make and save an Excel spreadsheet where column A is date-formatted, and cell A2 = A1 + 1. In your macro, launch the spreadsheet, type your variable into A1, arrow down to A2, copy A2 to the clipboard, put the clipboard value back into your macro variable, close spreadsheet.

[kevin]

Macro Express does not have built-in 'date math' functions but it does have the basic commands necessary to create them. This topic has been discussed several times on this forum. There are sample macros on our shared macros page that contain date math. And the PGM Functions Library, written by Professional Grade Macros, the host of this forum, contains routines that do date calculations. You may need to adapt one of the samples for your specific needs.

 

Here are some of the resources mentioned:

 

Past forum topics:

• Date calculation, Calculation based on date
  
• Calculating Dates, Need to find things 31 days in the past
  
• Date And Time, Is Ending Date&Time > Starting Date&Time
  

Shared Macros

• Date Time Difference Calculator
  
• Date Time
  

PGM Functions Library

• More Products for Macro Express
  
• PGM Functions Library™