Calculating net working days (without weekend) between two dates in Excel

Help
, ,
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.networkingdays

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

Thank you very much in advance!

2

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

3

Yes, it works by hand. I know, this is a Excel problem. Therefore I am looking for a UiPath solution.

4

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

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!

6

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:

  1. put an if which checks which date is smaller
  2. if date 1 is smaller, then calculate difference between date 1 and date 2
  3. 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

7

Hi @Andrzej_Kinastowski

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

8

No, net working days need to be just Monday - Friday.

9

the problem is that the program beaks off so that an If else is not possible anymore

10

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

This is what I was thinking:

image
image651×744 15.8 KB

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

image

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

image

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

Does this solve the issue?

Cheers
Andrzej

11

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

12

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

Regards
Nived N
Happy Automation

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?

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.