Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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.
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
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))
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.
Hi @Marticar001,
Try to concatenate Date with StartTime and Date with EndTime and then subtract one column from another:
Regards
Abduvali
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |