Compare dates from two excel sheets and apply condition

Help Activities
, , ,
1

Good night friends:

My main idea is to compare each of the dates in sheet 1 with each of the dates in book two.

If the date for book 1 is on sheet 2, subtract one day. “This new date” would repeat the loop to verify that the new date is not in sheet 2, if it is found, continue subtracting.

The problem I have is that I cannot subtract days from the date without affecting the entire flow

PS: Sheet two is a calendar with Saturdays, Sundays and holidays.

Cheers

HL

HLDates.zip (34.2 KB)

2

@Hernan_Lezama

Check below for your reference
https://forum.uipath.com/t/tutorial-how-to-calculate-difference-between-two-time-values-using-uipath/247565/3

Hope this helps you

Thanks

3

Thanks for your answer, I understand the video, but the issue is that first I need to change the date format (I do it as a string), after that I need to subtract days from that date, for this I need the date to be in DataTime format, but not just make it work.

Captura
Captura1774×836 57.7 KB

4

Hi @Hernan_Lezama, did u check whether the
Fech_Base varaible has output whose format is similar to yyyy.MM.dd

Because mostly when I am working with excel with dates I found that date format given in excel and output through differs like in my case if the date in excel is 18/09/2020 (dd/mm/yyyy) but the outputting date through write line activity would be

09/18/2020 00:00:00

so the format is changing actually there

Do check the output of date column and correct the format accordingly

Hope it helps you

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed:

5

I am thinking of working the dates directly as “datetime”, to be able to subtract days, I modified the formula but I get a message saying that I cannot do itDates.zip (36.0 KB)

Captura de pantalla 2020-11-20 15.39.49

6

@Hernan_Lezama - I am not exactly sure what you are trying here … I downloaded your recent zip file this is what i am seeing…

Read Range - You are reading only one cell value - and storing it in the datatable…

image
image1154×151 6.22 KB

For Each

And then using the datatable (which has only one value) you trying some assign activity , get row item etc etc…

Sorry i am not following here…

7

I put C4 so that it starts from that cell and doesn’t grab the header rows. The loop does work. Now it is set to the variable Base_Date as a string and you will see that it collects all of the cells in column “C”, but from “C4”. But what I want to move forward from here, is to be able to operate with these dates, for example subtract one day, and I cannot do that because they are String and not dataTime.Dates.zip (36.0 KB)

8

Captura2
Captura21805×904 63.4 KB

This would be the continuation to convert it to datetime and be able to add dates, but an error appears

9

Dates.zip (52.9 KB)

I improved the flow, but I still can’t use the “while”. I copy the python code and it works great:

I have two lists, I want no element of the first list to be in the second, if it happens, I must subtract the element of the first list by 1 using the While, until it meets the requirement of not being in the second list.

dates = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]

blackout_dates=[7,9,13,14,17,18, 19]

dates_ready=[]

print(dates)

for i in range(len(dates)):

    while dates[i] in blackout_dates:

        dates[i] -= 1

for x in dates:    

    if x not in dates_ready:

        dates_ready.append(x)

print(dates)

print(dates_ready)

Result: [1, 2, 3, 4, 5, 6, 8, 10, 11, 12, 15, 16, 20]