PowerApps DateDiff Showing Incorrect Hours for Cross-Midnight Shifts

Like (0) Share

Report

Posted on 2 Jan 2025 21:18:48 by AE-02012115-0

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!

Categories:

Using Apps Using Formulas

All responses (3)

Answers (0)

timl 34,988 Super User 2025 Season 1 on 03 Jan 2025 at 10:09:14

Like (0)

Report
PowerApps DateDiff Showing Incorrect Hours for Cross-Midnight Shifts

Hi AE-02012115-0

I tested your formula and it works for me. Therefore, I suspect the problem is likely due to the data that you're using. I also note the the ChatGPT/AI generated post beneath produces the same formula as you, so I assume the logic in the formula is correct. Would you be able to post the values that you're using?

In my test data, I have 3 sets of 8 hr shifts, one that spans 10PM to 6AM. The result from this is 24hrs (which is correct).

To troubleshoot this, you could add a column to calculate the difference per row. That would enable you to visually inspect which record produces the incorrect time difference calculation.

ClearCollect( colAttendanceWithHours, AddColumns( colAttendance, "HoursWorked", Round( DateDiff( DateTimeValue(ShiftStartTime), If( DateTimeValue(ShiftEndTime) < DateTimeValue(ShiftStartTime), DateAdd(DateTimeValue(ShiftEndTime), 1, TimeUnit.Days), DateTimeValue(ShiftEndTime) ), TimeUnit.Hours ), 2 ) ) )
Suggested answer

VASANTH KUMAR BALMADI 266 on 03 Jan 2025 at 04:35:13

Like (0)

Report
PowerApps DateDiff Showing Incorrect Hours for Cross-Midnight Shifts

Hi,

*** ChatGPT Generated Response ***

The issue stems from handling shifts that span midnight correctly, particularly in how DateTimeValue interprets the ShiftEndTime relative to the ShiftStartTime. Here's how you can resolve this issue:

Revised Formula for Calculating Hours Worked

The core logic needs to:

Identify if the shift crosses midnight (i.e., ShiftEndTime is earlier than ShiftStartTime).

Adjust the ShiftEndTime by adding 1 day in such cases.

Ensure ShiftStartTime and ShiftEndTime are parsed consistently as DateTime values.

Here’s an updated and corrected formula:

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" ) )

Explanation of Key Updates

Checking for Cross-Midnight Shifts:

The condition DateTimeValue(ShiftEndTime) < DateTimeValue(ShiftStartTime) detects if the shift spans across midnight.

If true, 1 day is added to ShiftEndTime to ensure it represents the correct date and time.

Parsing ShiftStartTime and ShiftEndTime:

DateTimeValue is used to convert the text fields into DateTime values. This is crucial to perform accurate time calculations.

Ensure that the SharePoint fields (ShiftStartTime and ShiftEndTime) store the time in a consistent format (e.g., yyyy-MM-ddTHH:mm:ss).

Summing the Hours:

DateDiff calculates the difference in hours between the adjusted start and end times for each record.

The results are summed across all records for the month.

Round and Display:

The total hours are rounded to two decimal places for clarity and formatted as a string.

Handling Edge Cases

Incorrect Time Formatting in SharePoint: Ensure ShiftStartTime and ShiftEndTime are stored in a consistent time format (HH:mm:ss or yyyy-MM-ddTHH:mm:ss). If not, preprocess the data to standardize it.

Empty Records: The formula handles cases where no records are present by displaying "No records for this month."

Debugging Steps

Use a Label to output the parsed DateTimeValue(ShiftStartTime) and DateTimeValue(ShiftEndTime) for a specific row to ensure correct parsing.

Test with various shifts (same-day and cross-midnight) to confirm accurate results.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!
ronaldwalcott 3,642 on 02 Jan 2025 at 23:01:11

Like (0)

Report

PowerApps DateDiff Showing Incorrect Hours for Cross-Midnight Shifts

Not quite understanding the approach of not including the date with the shift times. How does the employee interact with the system?

How are you capturing the clock in and out times? Are you tracking the actual datetime of clock in and out or removing the date?

Would the start and end datetime of the expected shift be used or the clocked in and out times be used with DateDiff to find the units in hours or minutes? Then apply rounding based on policies such as late start, expected shift time etc,, to calculate payable hours versus clocked hours.