Negative Duration

[Marticar001]

I have the following information

 *Date represents the date of the Begin Time

I made the following Custom column

Duration (Minutes) = [End Time] - [Begin TIme]

and then transformed that column to show Total Minutes

= Table.TransformColumns(#"Added Custom",{{"Duration (Minutes)", Duration.TotalMinutes, type number}})

There are instanses where [End Time] > [Begin TIme] resulting in a negative duration

Since Date only represents the Date for Begin Time

Is there a way to show the correct Duration in Minutes (and in Hours)?

[vcastello]

Hi @Marticar001

It seems to me that you are on Power Query/Query Editor as it looks like you are using M

Why don't you just  ....

1.- select both columns Date and Begin Time , right click and then merge with a space separator.  
2.- select both columns Date and End Time , right click and then merge with a space separator.
3.- convert to Date/Time both merged columns

4.- Substract both columns

Vicente

[MarcelBeug]

If you want to consider times only, you can add a custom column with formula:

Duration.TotalMinutes(#time(0,0,0)+([End Time]-[Begin Time])-#time(0,0,0))

[MarcelBeug · Accepted solution]

Maybe I should explain this solution:

Duration.TotalMinutes(#time(0,0,0)+([End Time]-[Begin Time])-#time(0,0,0))

Let's take 2 examples:

- from 9:00 AM - 3:00 PM

- from 9:00 PM - 3:00 AM

Each is 6 hours or 0.25 day,

[End Time] - [Begin Time] results in a duration of 0.25 and -0.75 respectively.

Adding this to #time(0,0,0) will result in a time.  #time will always take the distance from the lower integer to the value:

0 --> 0.25 = 0.25 (= 6:00 AM) and -1 --> - 0.75 = 0.25 (= 6:00 AM).

Both are the same as the result in Excel using MOD, e.g. =MOD(-0.75,1) returns 0.25.
In Power Query, Number.Mod(-0.75,1) gives -0.75, which is not we are looking for.

Another idea might be to take the DateTime from -0.75 and take the time part from that:
=Time.From(DateTime.From(-0.75)), but the fraction of a negative number is always added:
DateTime(0) = 30/12/1899 12:00 AM and DateTime(-0.75) = 30/12/1899 6:00 PM (or 18:00).
So that's not what we are looking for either.

Back to the formula: by subtracting #time(0,0,0) from the calculated time (both 6:00 AM), you get the duration from 12:00:00 AM to 6:00 AM (in this example) or 0.6:0:0.

Now the total minutes can be taken from this.

[Abduvali]

Hi @Marticar001,

Try to concatenate Date with StartTime and Date with EndTime and then subtract one column from another:

1. Column 1 = CONCATENATE(Date,StartTime)
2. Column 2 = CONCATENATE(Date,EndTime)
3. Column 2 - Column 1 = Duration (select duration time format for the column)

Regards

Abduvali

[TomMartens]

Hey,

from my point of view it's correct that the values are negative, this is due to the fact that 12:00 does not represent noon but instead midnight, but not the midnight from the day in your date column to the next day, but instead of the previous day.

This means that 12:00 AM marks the transition from the previous day to the current day. So to fix this you have to convert 12:00 AM to 12:00PM before the usage of DATEADD().

Regards

Tom