Calculate difference between two times

[kattlees]

I know there has been some other posts about this, but I think mine is a little different.

I have two columns

SCHTM is the time a surgery is supposed to start

SurgTime is the time it actually starts.

I need to find the difference in the two. The problem is, sometimes it starts late, sometimes it starts early. 

I have this in TimeDiff = DATEDIFF(patientinfo[SurgTime],patientinfo[SCHTM],MINUTE) but it gives me an error of "In DateDiff function, the start date cannot be greater than the end date"

Any help to calculate a positive or negative number?  Both columns appear as 7:15:00 AM or 2:15:25 PM

[v-ljerr-msft · Accepted solution]

Hi @kattlees,

Based on my test, the formula below should work in your scenario. It will return a positive number when starts late, and return negative number when starts early. 

TimeDiff = 
IF (
    patientinfo[SurgTime] <= patientinfo[SCHTM],
    - DATEDIFF ( patientinfo[SurgTime], patientinfo[SCHTM], MINUTE ),
    DATEDIFF ( patientinfo[SCHTM], patientinfo[SurgTime], MINUTE )
)

Regards

[vanessafvg]

Do an if statement @kattlees

if( date1 > date2, blank(), datediff statement)