I’m building an attendance tracker in PowerApps where employees can clock in and out. Some shifts start late at night (e.g., 11:14 PM) and end the following day (e.g., 6:53 PM).
I'm using the DateDiff function to calculate the total hours worked, but it’s returning incorrect values—739 hours, instead of the expected 19 hours for shifts crossing midnight.
Here’s the formula I’m using:
With(
{
varUserAttendance: Filter(
colAttendance,
Year(Created) * 12 + Month(Created) = Year(Today()) * 12 + Month(Today())
)
},
If(
CountRows(varUserAttendance) > 0,
Round(
Sum(
varUserAttendance,
DateDiff(
DateTimeValue(ShiftStartTime), // Start time
If(
DateTimeValue(ShiftEndTime) < DateTimeValue(ShiftStartTime),
DateAdd(DateTimeValue(ShiftEndTime), 1, TimeUnit.Days), // Add 1 day for cross-midnight
DateTimeValue(ShiftEndTime) // Same day
),
TimeUnit.Hours
)
),
2
) & " hour(s)",
"No records for this month"
)
)
Key Details:
- ShiftStartTime and ShiftEndTime are stored as Text fields in my SharePoint list.
- The formula works fine for same-day shifts, but fails for cross-midnight shifts.
- I need accurate calculations to support overnight work hours without these errors.
What I’ve Tried So Far:
- Adding 1 day to the ShiftEndTime for overnight shifts.
- Using TimeValue instead of DateTimeValue to focus only on time portions.
Problem:
None of these approaches have worked, and I’m still seeing inflated results like 739 hours instead of the expected 19 hours.
Question:
How can I correctly calculate hours worked for shifts that span midnight without encountering these errors?
Thanks in advance for any suggestions!
