cancel
Showing results for
Did you mean:
Highlighted
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

## 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.

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

Regards

2 REPLIES 2
Super User I

## Re: Calculate difference between two times

Do an if statement @kattlees

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

Proud a to be a Datanaut!
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.

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

Regards

Announcements

#### 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!

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

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