cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate duration in HH:MM between two dates numerically in a calculated column

Hi all

I'm having trouble finding a solution to this in the forums. I have two columns for datetime values for vessel arrivals and departures, DateTime Berthed and DateTime Departed Berth.

I need to find the duration between these two dates in hours and minutes, in a calculated column numerically so they can be summed by different categories (ie vessel type, month, etc) for berth utilisation.

Here is a snippet of the data:

When I use DATEDIFF I can only get either hours or minutes, ie:

Hours = DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], HOUR)

I don't want to have two columns that I have to concatenate as it then becomes a text value and I need to be able to sum up the column.

8 REPLIES 8
Super User

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

If you just subtract one column from the other you'll have the difference in days. If you multiply that by 24*60 you'll have it in minutes. You can then operate normally with it and convert it to hours and minutes or HH:MM format later on when required.

TimeDiffInMins = (Table1[Departed Berth] - Table1[Berthed]) * 24*60

Frequent Visitor

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

Hi there, thanks for your reply. When I use the following formula as suggested:

Hours = 'Berth Utilisation'[DateTime Departed Berth]-'Berth Utilisation'[DateTime Berthed] *24*60

The returned value for each line looks like this:

"System.FormatException: The string '-169521-12-11T05:15:00' is not a valid AllXsd value.

at System.Xml.Schema.XsdDateTime..ctor(String text, XsdDateTimeFlags kinds)

at System.Xml.XmlConvert.ToDateTime(String s, XmlDateTimeSerializationMode dateTimeOption)

at Microsoft.AnalysisServices.AdomdClient.FormattersHelpers.ConvertFromXml(String xmlValue, Type targetType, Boolean convertLocalTime)"

Super User

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

@rachel_g

That's weird. Are you using Power BI Desktop or something else? Are the columns of date type?

Anyway if the DATEDIFF was working, you could do the same with:

```TimeDiffInMins = DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], HOUR)*60 +
DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], MINUTE)```

Frequent Visitor

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

@AlB Thanks for that. Yes, I'm using PBi Desktop, and the column formats are Date/Time format.

The second formula you provided doesn't seem to give the correct value; for example I have DateTime Berthed of 15/01/2019 11:35:00 AM and DateTime Departed of 15/01/2019 6:45:00 PM and this formula gives me a value of 850

Hours =
DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], HOUR)*60 +
DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], MINUTE)
Super User

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

@rachel_g

OK,  try just

TimeDiffInMins = DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], MINUTE)

Super User

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

@rachel_g

By the way, you need the parentheses as I wrote them in the previous version. With them you won't get the error

TimeDiffInMins = (Table1[Departed Berth] - Table1[Berthed]) * 24*60

Frequent Visitor

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

Thanks, that just appears to give me the minutes.

Hours = ('Berth Utilisation'[DateTime Departed Berth]-'Berth Utilisation'[DateTime Berthed])*24*60

For a DateTime Berthed of 15/01/2019 11:35:00 AM and DateTime Departed Berth of 15/01/2019 06:45:00 PM I get the value 430 returned (the minutes)

Is there no way to get the hours and minutes in some kind of duration format that is numeric? Ie 7:10 instead of just having to accept 7 (if I use hours) or 430 for minutes?

Super User

## Re: Calculate duration in HH:MM between two dates numerically in a calculated column

@rachel_g

TimeDiff = 'Berth Utilisation'[DateTime Departed Berth]-'Berth Utilisation'[DateTime Berthed]

and choose the format HH:mm

I'm not sure how that will work out with addition and subtraction later. You'll probably run into problems as soon as it gets over 24h, as that will be shown as a day so I wouldn't recommend that

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,252 guests
Recent signins: