CODEWITHSUNDEEP
excel
user860511
user860511

Reputation:

Count the number of hours within a certain range on excel

I am wanting to enhance the functionality of my time sheet. I want to calculate if a person's hours falls into a range, and if so, count how many hours. For example:

A user clocks in on their time sheet: Tuesday: 05:00 - 04:00 i.e. - They worked 23 hours.

From the above time, I want to count how many hours they have worked between the time range 19:30 - 07:30.

Manually calculating this works out at 9.5 hours - or 09:30.

Many thanks.

Upvotes: 0

Views: 4248

Answers (2)

Andrew
Andrew

Reputation: 7880

This is what is working for me in all the cases I've tested:

Data cells:

A1: Range start | B1: Range end

A2: Clock In       | B2: Clock out

Formula:

=IF(B2<A1,0,IF(A2>B1,0,IF(A2<A1,IF(B2>B1,B1-A1,B2-A1),IF(B2>B1,B1-A2,B2-A2))))

The result is in days, so if you want it in hours, just multiply by 24.

=IF(B2<A1,0,IF(A2>B1,0,IF(A2<A1,IF(B2>B1,B1-A1,B2-A1),IF(B2>B1,B1-A2,B2-A2))))*24

If the times span more than one day, it's crucial for this to work that the cells include the date. In Excel, if you just enter the time, its (fake) date is 00/01/1900, so for the ending times to have the appropriate date, I specified the next day: 01/01/1900. Therefore, if you only have the time, you will have to do this as well.

But if you get a full DateTime from, for example, a database, then you won't have this issue, but you will have to modify the range cells to have the corresponding dates, or modify the clock cells to convert the date back to 00/01/1900, depending on your scenario. Please give some more details if you need help with this.

Upvotes: 0

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

Formula:

=IF(ClockOut<RangeStart,0,IF(ClockIn>RangeEnd,0,IF(ClockOut<RangeEnd,ClockOut,RangeEnd)-IF(ClockIn>RangeStart,ClockIn,RangeStart)))

Upvotes: 1

Related Questions