Forum Discussion

kattlees's avatar
kattlees
Post Patron
9 years ago
Solved

Calculate difference between two times

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

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

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

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

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

     

    Regards