cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rachel_g Frequent Visitor
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:

 

 PBi dates snip.PNG

 

 

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.

 

Can anyone assist please?

 

8 REPLIES 8
Super User
Super User

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

Hi @rachel_g

 

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

 

 

rachel_g Frequent Visitor
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.ConvertToDateTime(String s)

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

Super User
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)

 

 

rachel_g Frequent Visitor
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
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
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

rachel_g Frequent Visitor
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
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 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 3,252 guests
Please welcome our newest community members: