cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kattlees Post Patron
Post Patron

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Calculate difference between two times

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. Smiley Happy

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

 

Regards

View solution in original post

2 REPLIES 2
Super User I
Super User I

Re: Calculate difference between two times

Do an if statement @kattlees

 

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Microsoft v-ljerr-msft
Microsoft

Re: Calculate difference between two times

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. Smiley Happy

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

 

Regards

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors