Summing Working Hours in a Day

[AnnaDewitt]

Hello. I have a table as below:

Here, I want to calculate total working hours in a day. When there is no check-in and out during day, it is very easy to calculate but when there is check-in and out at different times in a day, I couldn’t find a way to calculate it. I tried to calculate it with excel formulas rather than RPA, but I am worse at Excel than RPA.

I actually calculated it with Sumif formula in Excel as below:

But I don’t want to see the underlined values because they are repeated day values which could be zero to not mislead people.

Sumif formula that I created is:
IF(SUMIF($B2:$B10;B2;$E2:$E10)=0;$E2;ETOPLA($B2:$B10;B2;$E2:$E10))

I am open to both ideas on Excel and UiPath. I just want to obtain for instance for 07.01.2020 - 05:39 + 04:13 = 09:52 hours and the value across repeated days could be zero. By the way I get these values with RPA and I also created this table with RPA so the project is RPA related. If I can manage this last thing, I will send this excel with an email by RPA.

[prasath17 (Guru)]

@AnnaDewitt - what is the requirement for ignoring the underline value ?

[AnnaDewitt]

I will send this excel table to the user, I want to make the table as clear and understandable as possible. Maybe I can try to delete repeated day rows.

[prasath17 (Guru)]

@AnnaDewitt - Sorry, I am still not following…

Ex: For 07.01.2020… you want only one row right 5.39+4.13 = 9.52…

I think i got it…how about Pivot? Can you share the excel sheet?

In the meantime, i will worksomething and share it with you…

[AnnaDewitt]

I am gonna try pivot table, thank you.

[prasath17 (Guru)]

@AnnaDewitt - I think , i am able to get the answer…Is this solution you are looking for?

[AnnaDewitt]

Yes

How can I do this? I am still struggling

[prasath17 (Guru)]

@AnnaDewitt - Here you go…

SumWorkHours.zip (120.1 KB)

Run the process, you will get the result in the D and E cells…

But I am not completely happy with this, because since there is time element involved I did some adjustments to get the results…I will see, if i can improvise this…

[AnnaDewitt]

I didn’t understand the code yet, but I will study on it. Thanks for your effort.

[ptrobot]

Another possible solution, if you want to do it in Excel. It checks if the rows above contains the same date. If true, it will leave the cell blank. If false, i.e. it’s the first occurrence, it will do the SUMIF().

=IF(COUNTIF($B$1:$B1; $B2)>0;"";SUMIF($B2:$B10;B2;$E2:$E10))

[prasath17 (Guru)]

@AnnaDewitt - Please refer this [link] for the explanation for the formula…

[prasath17 (Guru)]

@AnnaDewitt - I have updated the workflow to findtheLastRow, so that you dont have to hardcode the range anymore…

SumWorkHours_Updated.zip (121.0 KB)

Explanation: Below activity will find the last row # of the Column Date, which is 23.

Write Output:

Then I used this value in the range…like below…

Input.Sheet(“Sayfa1”).Range(“D2:D” + Saved.Values(Of Int32)(“Last Data Row”).ToString)

How to find this code:Saved.Values(Of Int32)("Last Data Row").ToString)

In the write Line, Click on the + symbol and choose advance editor which will give the clue ,how this variable has been saved from the last activity…

Now Instead of giveing D2:D100, we can use this variable to fill only up to the data.