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

[Anonymous]

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.

Can anyone assist please?

[AlB]

Hi @Anonymous

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

[Anonymous]

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)"

[AlB]

@Anonymous

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)

[Anonymous]

@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)

[AlB]

@Anonymous

OK,  try just

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

[AlB]

@Anonymous

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

[Anonymous]

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? 

[AlB]

@Anonymous

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