Get date of previous Wednesday - Tuesday period

[JoKerCoder (Joshua)]

I’ve seen a lot of posts similar to this but none with the right answer for my question. The robot I’m writing needs to pull reports for the most recent Wednesday - Tuesday period. I figured I could just get the previous Tuesday and -6 days to get the Wednesday before that. The issue I’m running into is that all the formulas I find in these forum posts get the Tuesday of the previous week (or current week). but i need the previous Tuesday, whatever week it happens to be in (the last concluded Tuesday, so, if today was Tuesday, I still want it to get the previous concluded Tuesday; in this case, Tuesday last week).

Edit: I don’t need the date range. I need the dates individually in separate variables.

Thanks in advance for your help!

[muktadir]

cdate(DateTime.Today.AddDays(-(DateTime.Today.DayOfWeek - DayOfWeek.Tuesday + 7) Mod 7)).ToShortDateString

@JoKerCoder try this once remove .ToShortDateString if you want date object

[ppr (Peter Preuss)]

maybe you can share some sample sets, so we can ensure the suggested solution

@JoKerCoder

but none with the right answer for my question

we shared also solutions with a configured offset array and would assume that it will serve

myDate.AddDays(-{x1,x2,x3,x4,x5,x6,x7}(myDate.DayOfWeek))

where
x1 - Offset from a Sunday to the targeted Tuesday / Wednesday
x2 - Offset from a Monday to the targeted Tuesday / Wednesday
…
x7 - Offset from a Saturday to the targeted Tuesday / Wednesday

[JoKerCoder (Joshua)]

I have tried that one and it works as long as it isn’t the current day. I want this to still get last Tuesday if today is Tuesday. Since today is Monday, I tried your recommendation with “Monday” instead of “Tuesday” to see if it would work but it gave me today’s date when I would want last Monday’s date in this case (because I need the last completed Monday).

[JoKerCoder (Joshua)]

@ppr What is your myDate variable supposed to be? I’m not sure how to implement your code: does it go in an Assign? default variable value? How does it know which part of the array to use?

Assume today is Wednesday, April 3. I want to pull data from last Wednesday (March 27) through yesterday (Tuesday, April 2).

Now assume today is Saturday, March 30. I still want to pull the same date range of data: March 27 - April 2

Now assume today is Tuesday, April 9, I still want it to pull the same date range of data: March 27 - April 2 (because even though today is Tuesday, it is not over, so it will be included in the next Wednesday-Tuesday period run (April 3 - April 9).

So far, the closest I’ve come to getting the correct date for the previous Tuesday is what @muktadir recommended above. However (using previous example), it does not work if today was Tuesday, April 9. That formula would return April 9, instead of the desired April 2.

Does that help clarify my question?

[JoKerCoder (Joshua)]

@muktadir I am a little confused. The code pasted at the beginning of your response says “dayofweek.tuesday” but the screenshot shows “dayofweek.monday” which of these are you suggesting I use? and is it supposed to be giving me the date of the previous Wednesday or Tuesday?

[muktadir]

@JoKerCoder
Please ignore the previous answer it had some error. the reason you see monday in screenshot is becasue i am testing the code with monday and since you need the date for tueday i am editing the expression

If i rn for monday

CDate(DateTime.Today.AddDays(-1 * ((DateTime.Today.DayOfWeek - DayOfWeek.Tuesday+ 7) Mod 7 + 7))).ToShortDateString()

If i run the code now for tuesday i would get 26 march. Hope I understood you correctly

[ppr (Peter Preuss)]

myDate is a variable of DataType: DateTime and represents e.g.

@JoKerCoder

Tuesday, April 9

Or

@JoKerCoder

Saturday, March 30

any Strings, if needed we parse into a datetime before

@JoKerCoder

How does it know which part of the array to use?

with DayOfWeek it does retrieve if myDate is Mon, Tue… and picks up the right offset

We would use an assign Activity
myStartDate | DataType: DateTime =

myDate.AddDays(-{4,5,6,7,1,2,3}(myDate.DayOfWeek))

and same code is working for:

as we have configured the right offset. for a Thursday, Friday, Saturday jus adapt the offset when not the same week wednesday is needed

the endDate is just an addition to the startDate

as you requested a separate variable:

@JoKerCoder

I don’t need the date range. I need the dates individually in separate variables.

[JoKerCoder (Joshua)]

@muktadir That is close but it only works if today were Monday and I was looking for last Monday. I’m still running into the problem of it not getting the correct Monday date if today were Tuesday and I wanted to include yesterday (Monday) in my query, your code would give me the date for 2 Mondays ago, which would be incorrect.

Basically, I need my code to result in the date of the most recent Tuesday that is NOT today (if today were Tuesday). I’m thinking I may need to just set the value of my date variable based on an If statement…

[JoKerCoder (Joshua)]

@ppr Ok I think I almost understand what you’re doing. However, it seems that you are expecting me to manually insert a date for the myDate variable, but I don’t want to have to enter a date each time this bot is ran. I’d like it to take today’s date (whatever day it is being ran) and find the appropriate date for the most recent Tuesday that is not today (if today were Tuesday), then I know I could just subtract 6 to get the Wednesday before that.

Secondly, I think you are saying that I need to change the array depending on when it’s ran, if I understand you correctly. Having to intermittently change the code like that is not desirable.

@ppr

jus adapt the offset when not the same week wednesday is needed

I’m thinking maybe an If activity that changes the date variables depending on what today is will probably be the right answer here…?

[ppr (Peter Preuss)]

@JoKerCoder

However, it seems that you are expecting me to manually insert a date for the myDate variable,

No, as it is a variable you can set the value as you need eg myDate = DateTime.Now or already change the code snippet

@JoKerCoder

I need to change the array depending on when it’s ran,

No we mentioned to specify on how to handle

@ppr

Thursday, Friday, Saturday

and if needed correct the offset 1 time within the implementation

@JoKerCoder

I’m thinking maybe an If activity that changes the date variables depending on what today is

not needed, just reread above

[JoKerCoder (Joshua)]

ok, finally understood how to implement your suggestion. I got the array to give me the correct Tuesday end date by ordering it as {5,6,7,1,2,3,4}. Then I subtract 6 from that to give me the correct Wednesday start date. Thanks, @ppr

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @JoKerCoder, Although you got the solution from @ppr I think I can give you an optimized way to find the previous day.

Just Use Modify Date Activity that’s it!

I have passed Tuesday to find the previous one & Wednesday to find the upcoming date,
You can use this activity accordingly!

Output:

Input Variable is Date i.e Now in Variable panel by default, Output can be as required a Text or Date!

I’m attaching the .xaml for your reference just check it once!
Main.xaml (10.0 KB)

Hope it will be helpful to you.

If you find this solution easier and more convenient then you can mark this post as a solution as well, it will be beneficial for others to get an easy & optimized solution!

Happy Automation!

Regards,
Ajay Mishra

[JoKerCoder (Joshua)]

Thank you for this! I didn’t know about this either, but @ppr’s reply is more of what I was looking for for this particular case. I wanted something I could set as a default argument value so I could bring in specific dates from Orchestrator if necessary.
I can already think of a couple of other of my processes that could use your suggestion. Thanks again.

[Ajay_Mishra (Ajay Mahendra Mishra)]

Sure @JoKerCoder , No worries!

Regards,
Ajay Mishra