Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two columns, "dateIN" and "dateDelivered" that are date/time types. I need to calculate the number of hours between the two, but only counting business hours (9am-6pm)
Example: if dateIN is 1/1/16 at 8am and the dateDelivered is 1/5/16 at 2pm. I need to calculate number of hours in that time frame between 9am and 6pm
Solved! Go to Solution.
I have two columns, "dateIN" and "dateDelivered" that are date/time types. I need to calculate the number of hours between the two, but only counting business hours (9am-6pm)
Using the formula below(just change "MyTable" to your table name) to create a calculate column should meet your requirement:
TotalWorkHour = VAR Days = DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY ) VAR InHour = HOUR ( MyTable[dateIN] ) VAR DeliveredHour = HOUR ( MyTable[dateDelivered] ) RETURN IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) ) + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) ) + ( Days - 1 ) * 9
Regards
Please refer to the formula below:
TotalWorkHour = VAR Days = DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY ) VAR InHour = HOUR ( MyTable[dateIN] ) + MINUTE(MyTable[dateIN])/60 VAR DeliveredHour = HOUR ( MyTable[dateDelivered] ) + MINUTE(MyTable[dateDelivered])/60 RETURN IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) ) + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) ) + ( Days - 1 ) * 9
Regards
I have two columns, "dateIN" and "dateDelivered" that are date/time types. I need to calculate the number of hours between the two, but only counting business hours (9am-6pm)
Using the formula below(just change "MyTable" to your table name) to create a calculate column should meet your requirement:
TotalWorkHour = VAR Days = DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY ) VAR InHour = HOUR ( MyTable[dateIN] ) VAR DeliveredHour = HOUR ( MyTable[dateDelivered] ) RETURN IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) ) + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) ) + ( Days - 1 ) * 9
Regards
what is 9 and 18 ya
how to set 830am - 5.30pm ?
counting business hours (0830am-5.30pm)
what is 9 and 18 ya?
The end result isn't correct:
For this one, it's showing -0.46 hours which would be 27 minutes of "business hours." 1. why is it negative? and 2. Since the request came in after 8pm and was sent at 9:20am, the value should be around 0.33
I feel like it's so close though!
Please refer to the formula below:
TotalWorkHour = VAR Days = DATEDIFF ( MyTable[dateIN], MyTable[dateDelivered], DAY ) VAR InHour = HOUR ( MyTable[dateIN] ) + MINUTE(MyTable[dateIN])/60 VAR DeliveredHour = HOUR ( MyTable[dateDelivered] ) + MINUTE(MyTable[dateDelivered])/60 RETURN IF ( InHour < 9, 9, IF ( InHour > 18, 0, 18 - InHour ) ) + IF ( DeliveredHour < 9, 0, IF ( DeliveredHour > 18, 9, DeliveredHour - 9 ) ) + ( Days - 1 ) * 9
Regards
Hi @bhmiller89. There might be a smoother way, but I got it working in Excel and then transferred to the Query Editor. Here's the end result with some made up data (before deleting the helper columns):
Most of the helper columns were because I ended up using the same calculations over and over again. That, and renaming the original columns for DateTime saved my sanity. The step-by-step:
1. For the DateIn column, select the DateTimeIn (your original dateIN), then go to the Add Column tab, click the Date dropdown, and select Date Only. Rename now, or at the end, or never if you delete it anyway.
2. For DateDelivered, repeat step 1 on the DateTimeDelivered column.
3. For TimeIn, select the DateTimeIn column, go to the Add Column tab, click the Time dropdown, and select Time Only.
4. For TimeDelivered, repeat step 3 on the DateTimeDelivered column.
5. For WholeDaysInBetween, select the DateDelivered column, then Ctrl-click to also select your DateIn column, click the Date dropdown and select Subtract Days. Then go into the formula bar and change each Duration.Days([DateDelivered] - [DateIn]) to each Duration.Days([DateDelivered] - [DateIn]) - 1
6. For DateInHours, go to Add Column -> Add Custom Column. Define as below. The first 9 is how many hours there are before you start counting time, and the second represents the 9th hour of the day, i.e., your clock start time.
=9 - (Time.Hour([TimeIn]) - 9)
7. Create another custom column for DateDeliveredHours, defined as below. The 9 is again how many hours there are before you start counting time, and the 18 is your clock end time, i.e., 6:00 PM.
=9 - (18 - Time.Hour([DateTimeDelivered]))
8. For Hours, add another Custom Column defined below. The outermost if statement makes sure your DateDelivered is at least the day after DateIn. If it's not (delivered same day), then subtract the end hour from the start hour and you're done. If the delivery date is past the In date, add up the DateInHours, DateDeliveredHours, and 9 hours for each full day in between the start and end. Notice DateInHours and DateDeliveredHours are not allowed to be greater than 9 or less than 0.
=if [DateDelivered]>[DateIn] then (if [DateInHours]>9 then 9 else if [DateInHours]<0 then 0 else [DateInHours]) + (if [DateDeliveredHours]>9 then 9 else if [DateDeliveredHours]<0 then 0 else [DateDeliveredHours]) + (if [WholeDaysInBetween]<0 then 0 else [WholeDaysInBetween]) * 9 else Time.Hour([TimeDelivered]) - Time.Hour([TimeIn])
9. You can now delete all of the 7 helper columns and be left with the number of Hours.
I got hung up on step 5. Ctrl+ click doesn't select multiple columns for me. I also don't see a date dropdown for "Subtract Days"
To select multiple columns, be sure you are clicking the column header, instead of a value in the column. If you click one column header, the whole column should be highlighted. Ctrl+clicking the second column should now show both highlighted.
Subtract Days will be grayed out without two columns selected. If it's not there at all, I recommend installing the latest version, as I'm assuming it was added since then. I'm on the July release currently and have it there.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |