Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bhmiller89
Helper V
Helper V

Excluding/Filtering by Certain times

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

2 ACCEPTED SOLUTIONS
v-ljerr-msft
Employee
Employee

@bhmiller89



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

tthours.PNG

 

 

Regards

View solution in original post

@bhmiller89

 

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

wh.PNG

 

Regards

View solution in original post

8 REPLIES 8
v-ljerr-msft
Employee
Employee

@bhmiller89



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

tthours.PNG

 

 

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: 

Capture.JPG

 

1.JPG

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! 

 

@bhmiller89

 

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

wh.PNG

 

Regards

KGrice
Memorable Member
Memorable Member

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

 

Hours.PNG

 

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.

 

SubtractDays.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.