CODEWITHSUNDEEP
excelfunctiondateif-statementexcel-formula
mostamazingname
mostamazingname

Reputation: 153

How to Calculate Time Between Two Dates with constraints taken into account (working hours)?

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!

Upvotes: 0

Views: 253

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 1

Related Questions