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

 

 

Highlighted
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