How to find specific cell (every week different row) and place information in it

Help Studio
1

Hello Forum users!

I have a problem how to define specific position for paste information.

Idea:
This process will be based on REF and I have in my mind dispatcher to recognize what is this week monday date(you can find same date in collum B in excel - attached picture). Store it in a queue. After that performer take this information and base on this find correct row(same date in collum B) in excel file(attached picture).

Do you have an idea how to store actual week´s monday date ( this week is calendar week 35 that mean that this week monday was 30.8.2021) in to queue ? I know how to store via “Reference/collection” of “Add Queue Item” but how to recognize actual week´s monday date is the question.

Thank you for your ideas

Here you can see the picture of file what am workin with

image
image1042×687 48 KB

2

Hi @jakub.blazek,

as far as I understand, you need to find the date of Monday in current week, right?
If yes, then you can try with
MondayDate = DateTime.Now.AddDays(-1*(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Monday)) mod 7).Date.ToString(“dd.MM.yyyy”)
where MondayDate is of type string.

2 Likes
3

Thats what I am looking for thank you soooo much!

attach picture for future use of others

image
image1426×723 60.6 KB

image
image880×331 22.4 KB

4

I am happy to hear that this is what solved your problem. You can mark my previous comment as solution then. :slight_smile:

Happy automation!

1 Like
5

Dear community,

I still have a problem I am able to describe what is a date of this week monday via: “DateTime.Now.AddDays(-1(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Monday)) mod 7).Date.ToString(“yyyy-MM-dd”)*” but via this I am not able to find specific date in excel file where is a date in same format mentioned. I think the problem is in “variable type” I asign this monday like a string but in excel file is system.datetime format. Do you know how to switch from string to datetime?

first picture: asign of this manday date:

image
image1872×857 78.8 KB

second picture how I can try to search for specific cell in excel:

image
image1404×672 29.9 KB

Expectation of result will be something like a cell position (“AT40”) or much better only row number (“40”).

Do you know how to do it?

6

Conversion from String to DateTime can be done using:
Datetime.ParseExact(MondayDate,“yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture)

Also, after extracting the date from the excel file I advice you to trim the value (the variable of type string), as this might be the reason why you don’t find specific date in excel file similar to the Monday’s date.

7

Yeah I am not able to use “lookup range” with datetime format…

So how to do it if I have date in excel? My question how to get output “row number” of this monday date?

Thank you for help

8

Is that what you are looking for?
DatesFromExcel.zip (8.4 KB)

and to get only the “row number” use cint(DateCell.Substring(1,1)) assigned to Int variable :slight_smile:

9

Hello ydimitrova,

yes that was my plan but the problem is if you have date format in excel this comand reply nothing back how yo ucan see on a picture.
image

10

In the example I have sent you the value from the excel is date format
image
after I run the project that I have sent you, this is the result:
image
In the project you can see the excel that I am using.

Check if the format in the excel file is the same as the format of the value that you have when getting the Monday’s date

11

Hello am doing same but my result is empty field format in excel has been changed to your proposal and still empty
:-/

12

Can you send me your excel or part of it (containing some of the dates)?

13

DatesFromExcelMySave.7z (40.6 KB)
here it is

14

Well, what you have sent me gives me the result:
image
:thinking:

15

My result still like this:
image

16

Okay, try to change the ModayDate format like DateTime.Now.AddDays(-1*(7 + (DateTime.Now.DayOfWeek - DayOfWeek .Monday)) mod 7).Date.ToString(“MM/dd/yyyy”)

17

Yeah that I already try but I think the problem is in complete date and view how UiPath read it because it is different from my “manual” excel open
image

18

Probably that is the problem. Have you tried to change the format in the excel with the chosen one in the photo:
image

I am out of ideas…

19

Hello again,

I solve it problem was in language set up of windows (I had originaly US english) now I switch it like this and it works!

image
image1162×690 85 KB

20

I am happy to hear that :slight_smile:

1 Like