calculate monthly working hours error

[ESAIT]

I am creating an attendance system in Power Apps that reads from a SharePoint List. In the SharePoint list i save the check in and out of all employees. i have a column that calculates the time difference between the check in and out. for example, when check in is 7:31 AM and check out is 5:06 PM. the working hours is 9:35. in my power app, i have a text box that displays the total working hours for each user. I have placed the following formula Text(Sum(Filter(CheckInOut, Title = glbUser, Mnth=Dropdown1_1.SelectedText.Value),WorkingHours)). but the result is 0. so I created a new coluumn that convers working hours to decimal number. meaning for 9:35 it is now 9.35 and i replaced the formula above with the following: Text(Sum(Filter(CheckInOut, Title = glbUser, Mnth=Dropdown1_1.SelectedText.Value),CombineTime))

Now the caclculation is done and the number is returned. yet the problem with this formula is that it deals with the numbers and decimal numbers and not as time. meaning, the total working hours is 143.46 while using the second formula the result is 140.6. so there are a 3 hours difference. this is again due to the fact that the calculation is done as numbers and not as time. how can i resolve this so as to caclulate the overall montly time as time and not as number

[rubin_boer]

hi @ESAIT 

considering you have an opening and closing datetime stamp you can use this:

Sum(Filter(CheckInOut, Mnth=Dropdown1_1.SelectedText.Value), DateDiff(yourStartTime, yourEndTime, Minutes) / 60)

[ESAIT]

@rubin_boer thank you for your assistance. i have a small question, yourstartTime and yourEndTime you mean by it the start date of the month and the final day of the month? for when i used your formula and substitute them with my columns: Sum(Filter(CheckInOut, Mnth=Dropdown1_1.SelectedText.Value), DateDiff(CheckIn, CheckOut, Minutes) / 60), the result was 4172.18333333

[rubin_boer]

hi @ESAIT 

the assumption was your data looks something like this:

{user | CheckIn | CheckOut | ...}

CheckIn = the time the user checked in for that day (e.g. 02/02/2023 8:35)

CheckOut = the time the user checked out for that day (e.g. 02/02/2023 8:35)

The DateDiff will calculate the difference between those times and summarize it for you.

[ESAIT]

@rubin_boer thank you dear

the check in is saved as 02/02/2023 8:30 AM and check out is saved as 02/02/2023 5:30 PM

[ESAIT]

@rubin_boer i have created a calculated column in sharepoint and named it WorkingHours that calculates the time difference between the Check in and out but when i export it, the result is saved with AM at the end which is why i believe when i run the first formula the result is 0 or empty. for example WorkingHours is saved as 9:35 AM, 10:25AM. i believe if AM is removed the formula can successfully add all the WokringHours. i tried a formula that removes the AM but still the result is always saved with AM

[rubin_boer]

@ESAIT Excellent, did it return the result you expected?

Some note:

[rubin_boer]

@ESAIT is it possible for you to change the data type in SharePoint to Number. That way you can sum that column to get the hours without the need to do anything to the column. i assume you have a date and time column now?

[ESAIT]

thank you dear the result of the formula is 4172.9 which is not the correct answer this means the employee worked over 4000 hours which is not possible the result should be around 152 hours

[ESAIT]

it is already saved as number. Thank you so much dear for all your help i have solved it in another way 🙂😁