Work Day/School Day Calculation

[svallie]

I was wondering if anyone could give me any insight on how to return a specific date (MM/DD/YYYY) in time from a time caculation of say 100 days forward or backward from a chosen date (MM/DD/YYYY)

 

For Example:

 

User Selects Date from prompt = 1/25/2007

Count how many days back prompt = 100

User chooses "Calendar Days" or "Weekdays" = Weekdays

 

Macro returns the answer of a date that is 100 Weekdays back from 1/25/2007 which would be 9/7/2006

 

The code below was posted (and pretty helpful) by someone that is doing something similar, but only based on the current date in time. I would like more flexibility in choosing any date from any date:

 

 

<VARIABLE SET DECIMAL Option="\x01" Destination="%D[1]%" Mask="FALSE" OnTop="FALSE" Left="Center" Top="Center" Monitor="0"/>

<VARIABLE MODIFY DECIMAL Option="\x03" Destination="%D[2]%" Value1="%D[1]%" Value2="5"/>

<VARIABLE MODIFY DECIMAL Option="\x02" Destination="%D[3]%" Value1="%D[2]%" Value2="2"/>

<VARIABLE MODIFY DECIMAL Option="\x00" Destination="%D[4]%" Value1="%D[3]%" Value2="%D[1]%"/>

<VARIABLE MODIFY DECIMAL Option="\x05" Destination="%D[4]%" Text_Variable="%T[3]%" Places="10"/>

<VARIABLE MODIFY STRING Option="\x04" Destination="%T[3]%" Variable="%N[1]%"/>

<DATE/TIME Format="ddd" Flags="\x82" Date="12/30/1899" Day_Offset="%N[1]%" Month_Offset="-0" Year_Offset="0" Hour_Offset="-0" Minute_Offset="-0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%T[2]%" IsDateVar="FALSE" _IGNORE="0x0002"/>

<TEXT BOX DISPLAY Title="T2" Content="%T[2]%" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

[stevecasper]

I was wondering if anyone could give me any insight on how to return a specific date (MM/DD/YYYY) in time from a time caculation of say 100 days forward or backward from a chosen date (MM/DD/YYYY)

 

For Example:

 

User Selects Date from prompt = 1/25/2007

Count how many days back prompt = 100

User chooses "Calendar Days" or "Weekdays" = Weekdays

 

Macro returns the answer of a date that is 100 Weekdays back from 1/25/2007 which would be 9/7/2006

 

The code below was posted (and pretty helpful) by someone that is doing something similar, but only based on the current date in time. I would like more flexibility in choosing any date from any date:

 

 

<VARIABLE SET DECIMAL Option="\x01" Destination="%D[1]%" Mask="FALSE" OnTop="FALSE" Left="Center" Top="Center" Monitor="0"/>

<VARIABLE MODIFY DECIMAL Option="\x03" Destination="%D[2]%" Value1="%D[1]%" Value2="5"/>

<VARIABLE MODIFY DECIMAL Option="\x02" Destination="%D[3]%" Value1="%D[2]%" Value2="2"/>

<VARIABLE MODIFY DECIMAL Option="\x00" Destination="%D[4]%" Value1="%D[3]%" Value2="%D[1]%"/>

<VARIABLE MODIFY DECIMAL Option="\x05" Destination="%D[4]%" Text_Variable="%T[3]%" Places="10"/>

<VARIABLE MODIFY STRING Option="\x04" Destination="%T[3]%" Variable="%N[1]%"/>

<DATE/TIME Format="ddd" Flags="\x82" Date="12/30/1899" Day_Offset="%N[1]%" Month_Offset="-0" Year_Offset="0" Hour_Offset="-0" Minute_Offset="-0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%T[2]%" IsDateVar="FALSE" _IGNORE="0x0002"/>

<TEXT BOX DISPLAY Title="T2" Content="%T[2]%" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

Very basically, you're going to want something that looks like this:

Date/Time: Set %dtvar% to a user prompted date/time
Variable Set Integer %N[1]%: Prompt
If Variable %N[1]% Is Greater Than or Equal To "5"
 Variable Modify Integer: %N[2]% = %N[1]% / 5
 Variable Modify Integer: %N[3]% = %N[2]% * 2
 Variable Modify Integer: %N[1]% = %N[3]% + %N[1]%
End If
Variable Modify Date/Time: %dtvar% = %dtvar% - %N[1]% Days
Text Box Display: Results

<DATE/TIME Format="dddd, MMMM dd, yyyy" Flags="\x93" Date="1/21/2009 5:13:34 PM" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Prompt="What date?" Left="Center" Top="Center" Monitor="0" Variable="%dtvar%" IsDateVar="TRUE"/>
<VARIABLE SET INTEGER Option="\x01" Destination="%N[1]%" Prompt="How many days back?" Mask="FALSE" OnTop="FALSE" Left="Center" Top="Center" Monitor="0"/>
<IF VARIABLE Variable="%N[1]%" Condition="\x04" Value="5" IgnoreCase="FALSE"/>
<VARIABLE MODIFY INTEGER Option="\x03" Destination="%N[2]%" Value1="%N[1]%" Value2="5"/>
<VARIABLE MODIFY INTEGER Option="\x02" Destination="%N[3]%" Value1="%N[2]%" Value2="2"/>
<VARIABLE MODIFY INTEGER Option="\x00" Destination="%N[1]%" Value1="%N[3]%" Value2="%N[1]%"/>
<END IF/>
<VARIABLE MODIFY DATE/TIME DateVar="%dtvar%" Option="\x01" LeftVar="%dtvar%" RightVal="%N[1]%" UseInteger="FALSE" MathOpt="\x00"/>
<TEXT BOX DISPLAY Title="Results" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 D/T = %dtvar%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

Of course, this assumes the date you're putting in at the prompt is a week-day. Basically what the macro is doing is taking the number of days

you want to backtrack, and then adding 2 days for every 5 (since for every 5 weekdays, there are 2 weekend days, so if the day you start counting

back is a Monday, and you count back 5 days, well, 5+2=7, so you end up with Monday as the day 5 weekdays earlier).

 

This will also only really work 100% if you're counting back by multiples of 5. Other variations could land you on a weekend. It would take quite

a bit more code to fix that... or code that I'm not familiar with right now. Or a trick... or better math, maybe?

 

Anyway, at the very least I hope this code helps you (or somebody else in the forums) to get the ball rolling.

[Cory]

I wrote this at the end but it should come first JiC: BTW you can do a lot of this in Excel. For instance check out the Workday function. It does exactly what you want to do: WORKDAY(start_date,days,holidays) Just check your help file for usage.

 

I do a lot of date manipulations like this and the logic can get quite convoluted when you start factoring leap years, holidays, and all that especially when you're doing divisions with remainders and all that. In reading your post I thought "Yeah I could do that but I'm not going to waste time writing it for him" when I thought of a much simpler way. Simply use the repeat to decay/advance the date. It's much more iterative but it's still light speed for your user and simple for you to understand. And you can use the date/time var manipulations. Now if it's just weekdays simply subtract the user specified number of days.

Variable Modify Date/Time: %Date% = %Date% - %User Days%

If not do this: Repeat the user specified number of times. If the current day is a weekday decrement the date. When it exits the repeat in a couple of mS you have your answer. To determine if it's a weekday take the integer date value for today, divide by seven and keep the remainder. EG today is 39834 and the remainder is 4(39834/7=5690 R4). As an odd coincidence we don't need to tweak this because obviously day one was a Sunday so the 4th day of the week is Wednesday. So the aforementioned logic is more like if the current day is greater than one decrement. This is because Saturday, being the last day in the week, has a remainder of zero and there fore Sunday has a remainder of one.

Date/Time: Set %Date% to a user prompted date/time
Variable Set Integer %Offset%: Prompt
Repeat Start (Repeat %Offset% times)
 Convert Date/Time to Decimal: %Date% => %Date Decimal%
 Variable Modify Decimal: %Date Decimal 4R% = %Date Decimal% / 7
 Variable Modify Decimal: Round %Date Decimal 4R% to 0 decimal places
 Variable Modify Decimal: %Date Decimal 4R% = %Date Decimal 4R% * 7
 Variable Modify Decimal: %Day of Week% = %Date Decimal% + %Date Decimal 4R%
 If Variable %Day of Week% Is Greater Than "1"
Variable Modify Date/Time: %Date% = %Date% - 1 Days
 End If
End Repeat
Text Box Display: Result

Find attached macro file.

Of course there are different approaches but my suggestion here is to keep it simple and understandable because the processor time is of no concern to you. You can also add some tweaks to this. For instance if the value they give you is negative subtract and it positive increment so they can go backward or forward. You can also account for holidays. Some are easy like "If the date is a weekday and the month is 12 and the day is 25 then skip" because it's Christmas. But a holiday like Thanksgiving day being the fourth Thursday is more difficult. You can either create a logical test (which is fun) or you can use a table of holidays for the foreseeable time horizon. They can be found online easily enough or you can make your own. If you make your own or modify and existing one you can put thing like company holidays in as well. The sky's the limit.

Date_decay.mex

[svallie]

Thank you for the assistance. This helps tremendously. I can calculate this function very easily in like 3 lines of Excel VB Code, but I am not sure how the new Pro External Script feature works. If anyone would like to contribute, let me know. In the meantime, I will incorporate this in my Macro Express code. Thanks for your time.