How to Assign Specific Dates of Current Month

Help
1

Hello,

I am trying to assign specific dates within the current month. For example, I am wanting the variables to be the 10th, 11th, 21st, and 22nd of the current month, (11/10/2018; 11/11/2018; 11/21/2018; 11/22/2018)

Thanks in advance!
Melanie

1 Like
2

I dont think there is any way to directly do it so you will need a function or a few steps.
The quickest way would be to use a switch:

dayDigit (INT) = cint(Datetime.Parse(datestring).Day.ToString.Last.ToString)

I.e. Get the last character of the day
Then use a SWITCH
Expression dayDigit (INT)
Case Default
suffix =“th”
Case “1”
suffix = “st”
Case “2”
suffix = “nd”
Case “3”
suffix = “rd”

Then use the suffix to create the date i.e. Datetime.Parse(datestring).Day.ToString + suffix

3

Thank @TimK - I am wanting to pull the actual date though, for example 11/10/2018 or 11/22/2018.

4

So you have “11th of November” as a string and you want to convert that?

Do you have an example of the variable you would have?

5

Hi,
Please check out this.
Date.xaml (7.3 KB)

6

Thank you @Anjali01 and @TimK - I found another solution to get these dates.

1 Like
7

Hi @melaniewatson
It might be useful if you shared your solution you came up with.

There is a way to change the day by creating a new DateTime like this:

New DateTime( Now.Year, Now.Month, 10)

Then, change 10 to whatever day you want.

Hope it maybe helps, maybe it doesn’t. :thinking:

EDIT, also to output it in a format just add .ToString("MM/dd/yyyy")

Regards.

1 Like
8

I don’t feel as though it is the most effective solution, but it works. The automation will be scheduled to run on the 11th, 22nd, and 1st of every month to pull 10 days worth of SAP data at a time. So on the 11th, the date range pulled from SAP will be the 1st through the 10th, for example 10/1/2018 to 10/10/2018.

I assigned the below variables:
yesterday (string) : now.AddDays(-1).ToString(“MM/dd/yyyy”)
tenDays (string): now.AddDays(-11).ToString(“MM/dd/yyyy”)
image
In the “Posting Date” section within SAP, I used a type into activity starting with tenDays, then another type into with yesterday.

HOWEVER, I like your method better! I am going to change some things around to use your solution. It really was what I was trying to achieve in the beginning. :grinning:

Thank you!!!

9

Quick question @ClaytonM
I am struggling to create one variable, the 22nd of the previous month. I know this is different from what I had originally said, but working through the flow, I realized some things needed to be changed.

When pulling the data for the last 10 days of the month, it will be pulled on the 1st day of the next month. For example, On 12/1/2018, data will be pulled from SAP for the date range 11/22/2018 to 11/30/2018.

I have the last day of the month variable assigned - lastDayMonth: New datetime(now.Date.Year,now.Date.Month,1).AddDays(-1).ToString(“MM/dd/yyyy”)

I considered using a similar formula, but subtracting -10 days, but worried that on months with 30 days the wrong value would be returned.

Hope this makes sense. Any thoughts? :thinking:

10

It sounds like you don’t want the last 10 days, but the days from 22nd to the last day of month.

You should also be careful, because let’s say the job fails on the 1st of the month and runs on the 2nd instead (for whatever reason), so how will you generate the range of days if you are using .AddDays() to determine the last day of the month. So, there might be a better calculation.

Let’s consider the .AddMonths() instead and also .AddYears() when January rolls around.
Let’s also try to pick the range of days that falls previously to the current date.

Global Declaration: dateUsed <as datetime> = Now // use CDate("11/22/2018") for testing other dates
Global Declaration: dateFormat <as string> = "MM/dd/yyyy"
Global Declaration: rangeDays <as array of int32> = { 1, 11, 22 }
Assign: firstIndex <as int32> = If(Array.IndexOf(rangeDays, rangeDays.Where(Function(x) dateUsed.Day >= x).Last)<>0, Array.IndexOf(rangeDays, rangeDays.Where(Function(x) dateUsed.Day >= x).Last)-1, Array.IndexOf(rangeDays, rangeDays.Last))
Assign: firstDate <as string> = New DateTime( dateUsed.AddYears(If(dateUsed.Month=1 And firstIndex=UBound(rangeDays), -1, 0)).Year, dateUsed.AddMonths( If(firstIndex=UBound(rangeDays), -1, 0) ).Month, rangeDays(firstIndex) ).ToString(dateFormat)
Assign: lastDate <as string> = New DateTime( dateUsed.AddYears(If(dateUsed.Month=1 And firstIndex=UBound(rangeDays), -1, 0)).Year, dateUsed.AddMonths( If(firstIndex=UBound(rangeDays), -1, 0) ).Month, If(firstIndex=UBound(rangeDays), DateTime.DaysInMonth(dateUsed.Year, dateUsed.AddMonths(-1).Month), rangeDays(firstIndex+1)) ).ToString(dateFormat)

So I coded inline If conditions to calculate the Year, Month, and Day correctly based on the previous day in the ranges using the current date or date of your choice.

I have tested this in the workflow uploaded:
exampleDateRange.xaml (11.7 KB)

Hope that helps.

Regards.

11

Hi @ClaytonM - Thanks for the example and I completely agree that the original way I had come up with wasn’t the best and did pose some risks as you described.

I will make the changes to my workflow taking these into consideration. Thank you as always for your help!

Mel:grinning:

12

@ClaytonM - I am still struggling with this one. While I understand your logic, it still doesn’t quite perform as I need it to and I unsure where to make edits to function as I need it to. In your example returns 11/11/2018 to 11/22/2018, when really I would need it to return 11/11/2018 to 11/21/2018.

To recap, this will run 3 times a month, pulling data out of SAP. Using the month of November for an example, on the 11/11/18, the date range pulled will be for 11/01/2018 to 11/10/2018. Then on 11/22/2018, the date range pulled would be 11/11/2018 to 11/21/2018. Finally on 12/01/2018, the date range pulled will be 11/22/2018 to 11/30/2018.

The 11/11/2018 and 11/22/2018 data pulls I have down, but I am struggling with how to assign the 22nd of the previous month when the data is pulled in the first of the new month.

Any help or suggestions would be greatly appreciated! Thank you!

13

Understood, it should take 11/11 to 11/21. I will check it as I probably had a mistake, and reply back with correction in a little bit.

I tested this with my example workflow and it worked, like it would take from the 3rd day number that was in the array, in this case the 22 day, and pull from the previous month and year if it is January. I will double check this also, but the intent was what you were looking for.

Thanks.

14

Ya, there was a mistake on the Assign that sets the last date at the end, it should have done a minus 1 to the rangeDays() so it takes the number 22 and does minus 1 of that.

Here is a test using the date “12/1/2018” showing that it takes previous month to the end of the month, regardless of how many days it has in that month (ie it will go to 2/28 or 2/29 for Feb even in a leap year)
image

Let me know if you need further explanation.

Regards.

15

exampleDateRange.xaml (12.0 KB)

16

Ok, running through this right now, one question - would I replace dateUsed with variable today that would return the date the automation would run? So, when this particular scenario would run today would be 12/1/2018. Is that correct?

17

yeah, you can use any DateTime type value there, so you can use Now or Today. I just used Now because that’s what I’m used to using. You can also hardcode a different value for testing like CDate(“12/2/2018”) and try it out. But, you will want to fix it back to either Now or Today when you are ready to run it off of the current date.

18

In the event that this had to run on the 3rd instead of the 1st, would the rangeDays prevent the correct data from being pulled?

19

Never mind! It works just the way I need it to. Thank you again!

Melanie

20

You’re welcome! @melaniewatson
yeah, wanted to make sure it will still work if it doesn’t run on the right date.

1 Like