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.
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.
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…
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().