Do something if date meets condition

Help
,
1

Hi,

I have column with dates:
image

The “breakpoint date” is the 21st every month. Which means that the “new” month starts the 21st every month.

Scenario 1:
Todays date is the 26th of October and the stuff below is based on this date. The “new” month has started.

Because it is the 26th I can keep all the dates until 30 November, everything after I can remove from the datatable or just dont process.

The next step(still 26th of October), The dates that are left in the column i want to filter based on another condition. Hard to explain but ill give it a try. Because it is the 26th I now want to keep dates from 1 October until the 30 November, The rest can be deleted or just not processed.

This needs to be dynamic, Example if the Current date was 26 July then it would be (1 July until the 31 August).

Scenario 2:

Todays date is the 5th of October and the stuff below is based on this date.

Because it is the 5th I can keep all the dates until 31 October, everything after I can remove from the datatable or just dont process.

The next step(still 5th of October), The dates that are left in the column i want to filter based on another condition. Because it is the 5th I now want to keep dates from 1 September until the 31 October, The rest can be deleted or just not processed.

This needs to be dynamic, Example if the Current date was 5th of July then it would be (1 June until the 31 July).

It is little hard to explain but I hope you get it somewhat.
I dont really know how to do this with the datetime syntax.

I could have static “Switch activity” but that list would get very big for every possible scenario.

2

Is it to complicated?

Should I divide the question/conditions in smaller parts?

3

Is your question like

  1. if date is less then 21 then dates to be processed are → 1st of previous month to last date of current month
  2. if date is more then or equal 21, then dates to be processed are 1st of current month to last date of next month
    is this correct?
4

if such is the case, then you can simply convert the date in excel to DateTime type and compare with the date and setup your filter condition

5

It is almost like that.

For the “1.” that you described it is like this:

If the date is less than 21 in the current month then I can keep all dates back in time and until the last date of the current month.(This is the dates that the robot will need to process)

The next step is the thing that you typed: "

  1. if date is less then 21(current month) then dates to be processed are → 1st of previous month to last date of current month

I do it in two steps because the first one is to get which ones i need to process and then for the next step i can process the dates but this time i can approve dates in the interval of “1.” without any modification in our internal system(This is another step of the whole “process”).

For the “2.” that you described it is like this:

If the date is greater or equal to 21 then I can keep all dates back in time and until the last date of the next month.(This is the dates that the robot will need to process).

The next step is the thing that you typed:

  1. if date is more then or equal 21(current month), then dates to be processed are 1st of current month to last date of next month

I do it in two steps because the first one is to get which ones i need to process and then for the next step i can process the dates but this time i can approve dates in the interval of “2.” without any modification in our internal system(This is another step of the whole “process”).

The dates is the first step and “hardest” after this i can use the other columns in my datatable.

Hope I didn’t confuse you.

6

“all dates back in time” this line is a bit confusing as to what you are referring to

7

“all dates back in time” = “All dates less than current date(current file/datatable column)”

8

Just convert the string type date in excel or database to date time format using DateTime.ParseExact and compare as per the need

9

Yeah, I dont really know how I will do it right now in a good way.

Lets say that the first date that i need to process is 2020-10-28.
I first need to check if this date meets the conditions above and then proceed.

I guess I first need a IF acitivity that checks if the date is within the correct interval.
And some more IF activities or Switch activites.

10

Assuming use of excel

  1. Get complete data in a variable using read range
  2. Filter the variable using filter data table
  3. Process the data table
  4. Save the processed data into excel again
11

I did the first steps like this:

image
image1539×971 47.4 KB

I have a For Each Row activity in the “GetTranscationData” part in REF, so i only process one row at a time in the image above.