0

I'm trying to calculate the time between two dates BUT also whilst taking into account a working hour constraint. I am trying to do this using Excel formulas.

Example follows below, where the working hour constraint would be between 08:00 -> 18:00. I.e only time which follows during these hours should be taken into account.

+---------------+-------------+----------------------+----------------------+
|     Start     |     End     |  Total Difference    | Within Working Hours |
+---------------+-------------+----------------------+----------------------+
| 1/1/19 20:20  | 1/2/19 9:30 | 13 hours, 10 minutes | 1 hour, 30 minutes   |
+---------------+-------------+----------------------+----------------------+

The question is how do I calculate the Within Working Hours column?

Thanks!

JvdV
  • 70,606
  • 8
  • 39
  • 70
mostamazingname
  • 153
  • 1
  • 1
  • 12
  • 2
    see my second part of my answer https://stackoverflow.com/questions/49161434/how-to-calculate-time-difference-in-excel-working-hours-only – Scott Craner Sep 16 '19 at 18:03
  • 1
    "only time which follows during these hours should be taken into account" would you also want to take working days into account? If so, @ScottCraner got you an answer in his link. – JvdV Sep 16 '19 at 18:17

1 Answers1

1

Use this that counts the days and multiplies that by the 10 hour working days and then adds for hours worked in the window on the work days:

=IF(DATEDIF(A2,B2,"d")>1,NETWORKDAYS.INTL(A2+1,B2-1,1)*10,0)/24+IF(AND(MOD(A2,1)<TIME(18,0,0),WORKDAY.INTL(A2-1,1,1)=INT(A2)),(TIME(18,0,0)-MOD(A2,1)),0)+IF(AND(MOD(B2,1)>TIME(8,0,0),WORKDAY.INTL(B2-1,1,1)=INT(B2)),(MOD(B2,1)-TIME(8,0,0)),0)

Right now we are only excluding weekends, but with NETWORKDAYS.INTL and WORKDAY.INTL one can include a range that will exclude holidays listed.

Then format the cell with a custom number format:

  [h] "hour, " mm "minutes"

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • thanks for this, but any idea why I get '221 hour, 30 minutes' when using your formula for the same start/end? – mostamazingname Sep 16 '19 at 19:43
  • 1
    My guess is your date time is `DD/MM/YYYY` instead of `MM/DD/YYYY` like mine. – Scott Craner Sep 16 '19 at 19:44
  • I've tried changing my datetime format to `MM/DD/YYYY hh:mm` like yours but still get 221 hour, 30 minutes – mostamazingname Sep 16 '19 at 19:46
  • 1
    You would need to change the local setting not the display format. But for now just enter `2019-01-01 20:20` in the first box and `2019-01-02 9:30` in the second and it should work. Excel leverages the local settings for input. You can display it any way you want but that does not change how excel expects the input. – Scott Craner Sep 16 '19 at 19:49
  • Oh okay I see.... that makes more sense! Thanks a lot for your help :) – mostamazingname Sep 16 '19 at 19:53