Hi,
I want to download reports from a web portal - simple enough, except the dates are embedded in the report are updated daily and sometimes weekly, with new file names and consequently, new dates. The reports I want to download are the top four in the list - that's a constant - and they are produced each Sunday. Is there a way to do one (or both) of the following:

1. Calculate the first day of the given week using a date/time variable or function. Format the date the way it appears currently, and change the report name.
2. Grab the file name from the anchor tag, href element and format the file name as required.

I haven't been able to find a DateTime variable that returns the first day of a given week, then the previous weeks date, and so on back four weeks.
Grabbing the file name from the href element of the anchor tag should be possible, but I haven't been able to accomplish it.
Has anyone encountered a similar problem with files that change daily, weekly, etc? I was told by support that wild carding is not supported in the Web Macro Recorder. I think that would make this kind of a problem trivial.

Any help is welcome.

The easiest way to solve 1) is to use .net. If you prefer formulas, you can do something like this (you have to test to see that it works properly)

1) Find the current day of the week: {DATE(WeekDayNumber|Monday)}

2) Use this to find how many days to subtract to get to monday of current week: (if today is sunday, the previous step is 7, so: -7 - 6 +7 results in -6 days,
if today is tuesday, previous step is 2, so: -2 - 6 + 7 results in -1 )

{MATH(Add|Integer|{MATH(Subtract|Integer|{MATH(Multiply|Integer|{DATE(WeekDayNumber|Monday)}|-1|)}|6|#0)}|7|#0)}

3) Use result in step 2 to get to monday in current week:

{DATENOWADD(Days|{MATH(Add|Integer|{MATH(Subtract|Integer|{MATH(Multiply|Integer|{DATE(WeekDayNumber|Monday)}|-1|)}|6|#0)}|7|#0)}|dd.MM.yyyy)}

4) Subtract any number of weeks that you want (this is the final formula)

{DATEADD(Weeks|{DATENOWADD(Days|{MATH(Add|Integer|{MATH(Subtract|Integer|{MATH(Multiply|Integer|{DATE(WeekDayNumber|Monday)}|-1|)}|6|#0)}|7|#0)}|dd.MM.yyyy)}|dd.MM.yyyy|-1|dd.MM.yyyy)}

The parameter to replace is the -1 at the end of the formula, so in this example i have sutracted one week. Change formatting if you like

Thomas

Thanks for the reply.
Couple of questions:
1. How would you change the formulas to make Sunday the first day of the week?
2. I was told by support that you can insert a .NET step before the current one and use it in the Web Macro step I listed to figure out the dates. Here's the response from support:
"There is no built in method for this but you could use the .NET Task before to calculate this date and then you can use the output of that Task: {TASK(PrevTask|StdOut)} in the Web macro Task. "
If I decide to go with step 2, who would it be implemented?

Thanks!

1) Just change the text where it says Monday to Sunday.

2) Add either a .NET code execute task, or an assembly. With assemblies you write code in a class library, compile it, and reference the resulting dll. The advantage of using an assembly is that you can unit test your code.

I you want to use a .Net task, just add it, and write your code in this section:



Make sure it returns the formatted date you have calculated. The easiest way is to write the code in Visual Studio, make sure it works, and the copy it into this section. In the following task you fetch the output of this task as described by support.

I don't think your first post for finding the first day of each week would work because it depends on knowing the current day of the week. I need the function to be totally dynamic: it needs to know the current day of the week and not rely on me to input it (unless I'm misunderstanding your function). So it would need to know the current date through some function/variable and use this to find the date of the first day of that week. The entire function needs to be dynamic because the dates in the reports change each week.

Any ideas?

Thanks again!

The function posted finds the current day of the week without input from the user. The only input is how many weeks you want to subtract.

This formula {DATE(WeekDayNumber|Monday)} gives you the day of the week we are currently at, using Monday as the first day of the week. Paste it into the variables box and you will see it.

To avoid having to copy the formula several times, put it in separate job/task, and call using a Job/Task control with number of weeks as the parameter.

I checked the output of your formula in the Variables Window and it showed todays date, July 5, 2017, not July 3, 2017 which would be the Monday of the current week (see screenshot). I checked your formula and can't find where the error is. Can you check your formula and correct where necessary?

You picked the formula from step 3. Use the final formula in step 4.

Ok, I tried the final formula and the output is the Wednesday of last week - 6/28/2017. I was expecting 7/3/2017, which would be the first Monday of this week (see screenshot). Could you revise your formula to return the date for Sunday of the current week (7/2/2017 for this week). NOTE: todays date/day of the week is not known. It must be calculated dynamically.

I think you may have misunderstood my question, because you base your whole formula on knowing the day of the week (I think in your case you used Monday because you responded on a Monday:

>>"1) Find the current day of the week: {DATE(WeekDayNumber|Monday)}"

I need a formula where the date of the week must be dynamically retrieved and used to calculate the Sunday date for that week. There are no date variables that are known in this case. Then from that result, I need a formula to determine the 3 previous Sundays.
Is this still possible to do using formulas?

That's what the formula does.

This part below is only used to determine if Sunday should equal WeekDay = 1. If you replace with Monday, then Monday = 1 and Sunday = 7.
{DATE(WeekDayNumber|Sunday)}

Today is Wednesday, and since the formula above has Sunday equals 1, then todays WeekDay = 4.



Just change to this for sundays (i replaced monday with sunday):

{DATEADD(Weeks|{DATENOWADD(Days|{MATH(Add|Integer|{MATH(Subtract|Integer|{MATH(Multiply|Integer|{DATE(WeekDayNumber|Sunday)}|-1|)}|6|#0)}|7|#0)}|dd.MM.yyyy)}|dd.MM.yyyy|0|dd.MM.yyyy)}

The formula above gives you the previous sunday. replace with -1 and you get the sunday before that and so on.

If you check the output from the Variables window - I pasted your current formula in and the output is shown below it - you'll see that I'm getting the date 6/29/2017 which is last Thursday. There appears to be a flaw somewhere in your logic, at least according to the Variables window from VisualCron. Can you pinpoint where or what the problem is? The date should be 7/2/2017, which is the Sunday of the current week.

Ok. I see it was setup to work for mondays specifically. I have to look at this tomorrow

Thank you so much! I really appreciate your help.

Please remember that I'm looking for a formula that will return the date for Sunday of the current week and the dates of the previous 3 Sundays. This should change dynamically every time we move to the next week without changing the formula.

I'll look forward to your revision tomorrow.

ah screw it. Use .Net instead. I found it hard to nest all these formulas 🙂 Note that this only work for sundays as it is the last day of the week (that's why it works to only look for the next sunday, and not previous, in any given week). If you want to change to some other day, the code must be changed.

1) Add a .Net task. Paste in this code inside the "public class Test". You can rename the class from Test to something else.

public static string GetSundayInWeek(int addWeeks)
{
DateTime asOf = DateTime.Today.AddDays(addWeeks * 7);
return FindNextSunday(asOf).ToString("dd.MM.yyyy");
}

private static DateTime FindNextSunday(DateTime asOf)
{
DateTime locaDateTime = asOf;
while (locaDateTime.DayOfWeek != DayOfWeek.Sunday)
locaDateTime = locaDateTime.AddDays(1);
return locaDateTime;
}


2) Press Compile, and then Refresh methods. In the paramater section add the number of weeks you want to subtract or add. Click run to test the code



3) Call this Job/task from a Job/task control and supply the relevant paramater there.

Hi,

Thanks for the .net code. I'm in the process of learning how to use the .net task. I know this task would be placed before the Web Marcro task, so I'm unclear how to use the value(s) returned from the .net task in the Web Macro. Can you give me an simple example of #3:

"Call this Job/task from a Job/task control and supply the relevant paramater there."

Thanks again for all your help.

Put it in a separate job. Add a job variable called addWeeks, and replace the parameter in the .Net task with this variable (this makes it dynamic) . Make sure you uncheck the 'Validate value' when you replace the parameter.



The you call this job from the web macro job. Use the Job/Task control, find the job you created in the previous step ( I called it SundayTest), click on Job variables, then click on Get current variables. This will get the job variable from the job you are calling. Add the number of weeks you want to subtract.

Quick question - does your .Net code have the capacity to return the current week Sunday date? I'll need that in addition to the previous 3 Sundays.

I'm also confused about why you would put the variable in a separate job rather than the job that holds the tasks? Or maybe I misunderstood and you meant put it in the Job the Tasks are already in ...

Just pass in 0 and it gives you the sunday of the current week.

You don't have to put it in a separate task. Thats only to make it reusable for other jobs.
I'm of work for a while, so you will have to ask support from now on. Good luck!

Hi,
Thanks for all your help. I feel like I'm close to a solution, but missing some details due to lack of familiarity with the product.
To circle back to the main reason I'm looking to use a .net task is I have 4 reports, each of which has a date in the name. The first report has the date of the current Sunday, the second report the date of the Sunday prior, and so on two more times. Your .Net code does exactly what I need. But it seems like I need four instances of the addWeeks variable - each with a different value - to get the 4 dates.

When you have the time, could you show me the steps to adding the addWeeks procedure as the current Job or Task variable (I have your code in the first task and the Web Macro in the second task. I would need to append the variable(?) to the end of each of the 4 reports in order to accomplish my goal. Do I need four instances of addWeeks (4 variables) or can I use addWeeks like this "addWeeks(-1)", "addWeeks(-2)", etc.

I really appreciate your help. I'll be out of the office for a few days, so there's no rush for an answer.

Thanks again!