Scribd
Upload
34 views2 pages

Working Hours Between Two Dates (Calendar Formula 2)

This document outlines a method for calculating the number of working hours between two dates using a calendar with defined work periods, breaks, and hours. It shows a sample calendar with 6 periods between hours 0-22 and calculates the working hours between starts of 8:30 AM and 6:30 PM as follows: 1) It finds the matching periods for the start and end times in the calendar. 2) It looks up the start and end times for those periods. 3) It calculates the working hours by taking the difference between the cumulative hours at the end period for the stop time and the start time. The process is then condensed into 4 formulas to simplify the calculation.

Uploaded by

sahilsahni20
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
34 views2 pages

Working Hours Between Two Dates (Calendar Formula 2)

This document outlines a method for calculating the number of working hours between two dates using a calendar with defined work periods, breaks, and hours. It shows a sample calendar with 6 periods between hours 0-22 and calculates the working hours between starts of 8:30 AM and 6:30 PM as follows: 1) It finds the matching periods for the start and end times in the calendar. 2) It looks up the start and end times for those periods. 3) It calculates the working hours by taking the difference between the cumulative hours at the end period for the stop time and the start time. The process is then condensed into 4 formulas to simplify the calculation.

Uploaded by

sahilsahni20
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Working Hours Between Two Dates (Calendar Formula 2)

because according to Calendar 2 there is a 15 minute morning break and a 30 min


lunch break in between.
working hours (days x 24) there are between the two points. The calculation is very
similar to that in Section 6:

Working
Period Working Hours so
Number Begin End Hours far (Cum)

1 0 0 0 0
2 8 10 2 2
3 10.25 13 2.75 4.75
4 13.5 15.5 2 6.75
5 15.75 18 2.25 9
6 19 22 3 12

The first 9 stages of the calculation locate the start on the calendar, and they are repeated for the stop:

Start Stop
8.5 18.5
Stages of the calculation: (8:30 AM) (6:30 PM)

8:30 AM is after period 2 begins, (6:30 PM is after period 5 begi 2 5 =MATCH(H20,B10:B15)


8:30 AM is after period 1 ends, (6:30 PM is after period 5 ends) 1 5 =MATCH(H20,C10:C15)
8:30 AM (6:30 PM) lies between the beginning and end of period 2 6 =H23+(H23=H24)
period 2 begins at 8:00 AM, (period 6 begins at 7:00 PM) 8 19 =INDEX(B10:B15,H25,1)
period 2 ends at 10:00 AM, (period 6 ends at 10:00 PM) 10 22 =INDEX(C10:C15,H25,1)
you can start at 8:30 AM, (stop at 7:00 PM) because it's within a 8.5 19 =MAX(H26,H20)
the number of hours from the start to the next break 1.5 3 =H27-H28
cum hours at end of period 2, (12) 2 12 =INDEX(E10:E15,H25,1)
the start is at cum hour 0.5 of the calendar, (the stop is at cum h 0.5 9 =H30-H29
working hours between the Start and Stop, (9 - 0.5) 8.5 =H31-G31

With a series of substitutions, the 19 formulas can be condensed into 4 as follows:

Calc 4 2 =MATCH(G20,B10:B15)+(MATCH(G20,B10:B15)=MATCH(G20,C10:C15))
Calc 5 0.5 =INDEX(E10:E15,B37,1)-INDEX(C10:C15,B37,1)+MAX(INDEX(B10:B15,B37,1),G20)
Calc 6 6 =MATCH(H20,B10:B15)+(MATCH(H20,B10:B15)=MATCH(H20,C10:C15))
Hours 8.5 =INDEX(E10:E15,B39,1)-INDEX(C10:C15,B39,1)+MAX(INDEX(B10:B15,B39,1),H20)-B38
(H20,B10:B15)
(H20,C10:C15)

B10:B15,H25,1)
C10:C15,H25,1)

E10:E15,H25,1)

B37,1),G20)

B39,1),H20)-B38

You might also like