Excluding/Filtering by Certain times

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.