Forum Discussion
DateDiff giving negative result when the next date-time is less than 24 hrs difference
This is for a calculated column.
Whenever the [exit time] is the next day, but within 24 hours of [enter time], I get a negative result for minutes.
All other aspects, except this, are working fine.
Minutes =
VAR _NonWorkingDays =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Day Type] <> "Work Day"
)
VAR _WorkTimeElapsed =
DATEDIFF(AuditLog[Enter_Time],AuditLog[Exit_Time], MINUTE) - (_NonWorkingDays* 24 * 60)
RETURN
_WorkTimeElapsed
4 Replies
- SachinNandanwar
Impactful Individual
I think it is because your exit time is before enter time in the 24 hour period.
Can you post some data ?Minutes Enter_Time Exit_Time -32 2/19/2024 8:47:00 AM 2/20/2024 8:15:00 AM -153 6/19/2024 11:32:00 AM 6/20/2024 8:59:00 AM -328 2/19/2024 2:18:00 PM 2/20/2024 8:50:00 AM - SachinNandanwar
Try something like this in the Return statement
RETURN IF( _WorkTimeElapsed < 0, _WorkTimeElapsed + (24 * 60), _WorkTimeElapsed )
