aaydin
(Ahmet Aydin)
November 27, 2020, 7:10am
#1
Dear all,

I try to calculate the net working days between two different days in a Excel sheet. The weekend days Saturday and Sunday have to be considered.
I have already tried to enter the excel formula ("=networkdays(…)") with the activitiy “Write Cell” and “Auto Fill Range”. But this does not work due to errors in Excel.
Excel recognizes any kind of errors and deletes formula automatically. That’s why I need a different solution.

How can I solve it via UiPath?
Are there any other activities or functions which can help me?

Thank you very much in advance!

Hi,

Does the formula work when you enter it by hand?
From your screenshot it looks like the dates in columns K and M aren’t interpreted as numbers by Excel itself, so it may be and Excel problem rather than a UiPath one.

Cheers
Andrzej

aaydin
(Ahmet Aydin)
November 27, 2020, 7:34am
#3
Yes, it works by hand. I know, this is a Excel problem. Therefore I am looking for a UiPath solution.

OK, then what you could do is read the Excel data into a datatable, and then calculate workdays in UiPath. This component could help you do that: Working Days Calculation - RPA Component | UiPath Marketplace

You may need to use Convert.ToDate (or cdate for short) to translate data from those columns into proper dates.

Cheers
Andrzej

aaydin
(Ahmet Aydin)
November 27, 2020, 1:39pm
#5
Hey,

thank you! But the only problem is, that I get an error when the result of net working days (–> int) is negative.
How can I solve this?

Thanks!

I must say I do not know how this component works, as we have our own that we use for business days calculation.

But the simplest way would probably be:

put an if which checks which date is smaller
if date 1 is smaller, then calculate difference between date 1 and date 2
if date 2 is smaller, then calculate difference between date 2 and date 1 and then multiply the result by -1 to make it negative
Would that help?

Also, I think it would be worth to give the feedback to the component’s author, so he can adjust his component accordingly.

Cheers
Andrzej

Hi @Andrzej_Kinastowski

Do u need to include the sat and sun in the net working days?

aaydin
(Ahmet Aydin)
November 27, 2020, 3:01pm
#8
No, net working days need to be just Monday - Friday.

aaydin
(Ahmet Aydin)
November 27, 2020, 3:02pm
#9
the problem is that the program beaks off so that an If else is not possible anymore

Not if you do the “if” before invoking the “Calculate working days” activity.

This is what I was thinking:

In the “Calculate DD->ED” you put arguments like this:

And in “Calculate ED->DD” the other way around:

Here’s my code: networkdays.zip (8.5 KB)

Does this solve the issue?

Cheers
Andrzej

Hi @Andrzej_Kinastowski do we need to include the start and end dates into our calculation

Hi check this workflow
Hope it may solve ur problem
excel.zip (41.9 KB)

Regards
Nived N
Happy Automation

aaydin
(Ahmet Aydin)
December 11, 2020, 6:33pm
#13
Hello,

sorry for the delayed feedback.
I am going to use your solution. Thank you very much!

But the only problem I have, is the German DateTime Format.
CDate does not recognize this format. I get always errors.

How can I solve this problem?

system
(system)
closed
December 14, 2020, 6:44pm
#14
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.