DateDiff giving negative result when the next date-time is less than 24 hrs difference

[noannish]

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

[SachinNandanwar]

I think it is because your exit time is before enter time in the 24 hour period.

Can you post some data ?

[noannish]

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

[Anonymous]

Hi  @noannish ,

According to your statement, I think your result is correct.

Working backwards from the results you gave, I think the _NonWorkingDays = 1 and (_NonWorkingDays* 24 * 60) in your code will return 1440. 

Since I don't know exactly what your Calendar table looks like, if there is a issue with this part of the result, please check it.

Please make sure the datediff(minutes) between [Enter Time] and [Exit Time].

I think the datediff(minutes) in your sample are all less than 1440, so datediff(minutes) - (_NonWorkingDays* 24 * 60) will return negative result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SachinNandanwar]

Try something like this in the Return statement

RETURN IF(
_WorkTimeElapsed < 0,
_WorkTimeElapsed + (24 * 60),
_WorkTimeElapsed 
)