Subtract dates in excel with conditionals

Help Activities
, ,
1

Good afternoon friends, please I would like to know if you can give me a little hand with this problem.

Thank you very much in advance

For all the dates to be evaluated in sheet “SBS” and column “A” (Initial_Date) the rule is to subtract one day to make it a valid date (Final_date); As long as it does not fall on Saturdays, Sundays and holidays, those dates are in the “Blacklist” sheet, if it falls on some of those days, one more day will remain. Finally the resulting date is written in the sheet “SBS” column “B” “Final_date”.

The result is in column B
image

Apparently uipath takes the data of each cell as an object and I cannot use it as dates to be able to do the subtraction

image
image742×1475 41.9 KB

subtract_dates.zip (28.5 KB)

2

Hi,

We need to use DateTime.Parse ( or DateTime.ParseExact) to convert it to DateTime type as the following.

img20210930-2
img20210930-2703×466 12.8 KB

Then use AddDays method for DateTime variable like
Initial_date = Initial_date.AddDays(-1)

Regards,

1 Like
4

Could you please brief this requirement ??

5

@gh_lzm - Please check this… subtract_dates.zip (28.6 KB)

This is my output

image

6

Good afternoon @prasath17, thank you in advance for your answer, your solution is very practical, but in the Blacklist there would also be holidays, as an example I have created an extra date in the column “SBS” 14/09/2021, it is a Tuesday. I have also created one more date in the sheet “Blacklist”, “13/09/2021”. Then the bot would have to go back one day but since it is 13/09/2021 it is a holiday, it goes back to Friday 10/09/2021. Please, how could I solve that?
I will upload the example excel.

datetime.xls (58.5 KB)

7

Your attachment was not added correctly. But I think I got your requirement now. Let me tweak the code again.