**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula that counts the number of hours in a given time range within another given time range. For example, If someone works from 6:00-17:00, I need to know how many of those hours are between 15:00-7:00

Solved by D. Y. in 37 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/06/2018 - 06:26

Hi…Welcome to Got It Pro

User
18/06/2018 - 06:27

hello

Excelchat Expert
18/06/2018 - 06:27

From your example, I am setting out an illustration and then I will create the formula to count the number of hours that are within.

Excelchat Expert
18/06/2018 - 06:27

Is that ok?

User
18/06/2018 - 06:28

yes. i need a formula that will also work in excel

Excelchat Expert
18/06/2018 - 06:28

Ok..let me provide you an excel solution.

User
18/06/2018 - 06:28

thank you

User
18/06/2018 - 06:32

approx how long will this take?

Excelchat Expert
18/06/2018 - 06:33

This will take me a couple of minutes.. I am on it

Excelchat Expert
18/06/2018 - 06:35

[Uploaded an Excel file]

Excelchat Expert
18/06/2018 - 06:36

The formula is in cell E2. Please review

User
18/06/2018 - 06:36

i am waiting for the document to open

Excelchat Expert
18/06/2018 - 06:36

sure..please take your time

User
18/06/2018 - 06:37

is there a way to have the product in E2 as a number of hours instead of as a time?

Excelchat Expert
18/06/2018 - 06:38

ok..you mean the final hours?

User
18/06/2018 - 06:38

yes

Excelchat Expert
18/06/2018 - 06:39

Sure..let me upload the new file

Excelchat Expert
18/06/2018 - 06:39

Is this fine?

[Uploaded an Excel file]

User
18/06/2018 - 06:41

waiting for it to open

User
18/06/2018 - 06:41

what is the new value you have in E2

Excelchat Expert
18/06/2018 - 06:41

It is the hours as in decimal

Excelchat Expert
18/06/2018 - 06:41

So 3 hours is overlap

Excelchat Expert
18/06/2018 - 06:42

The cell formatted as General

User
18/06/2018 - 06:42

the value in B2 is supposed to be 5:00pm, not 6:00, when i changed it the value in E2 is no longer correct

Excelchat Expert
18/06/2018 - 06:42

So 15:00 to 18:00 is 3 hours overlap

Excelchat Expert
18/06/2018 - 06:43

If you change B2 to 17:00, it will give 2 hours

User
18/06/2018 - 06:44

correct, but it should be three hours because there is an hour of overlap from 6-7 am and then 2 hours of overlap from 3-5 pm

Excelchat Expert
18/06/2018 - 06:44

I have just put it up in the blank sheet as well

Excelchat Expert
18/06/2018 - 06:44

oh I see it

Excelchat Expert
18/06/2018 - 06:45

Let me amend the formula

User
18/06/2018 - 06:46

thank you

Excelchat Expert
18/06/2018 - 06:51

Could you please put this in cell E2.

Excelchat Expert
18/06/2018 - 06:51

=HOUR(IF(C2>=A2,IF(OR(D2<=B2,D2>=B2),B2-C2,0)))+HOUR(IF(C2>=A2,IF(D2>=A2,D2-A2,0)))

User
18/06/2018 - 06:54

that seems to work

Excelchat Expert
18/06/2018 - 06:54

Perfect

Excelchat Expert
18/06/2018 - 06:54

Is there anything else I can help you with this query?

User
18/06/2018 - 06:54

that is all. thank you

Excelchat Expert
18/06/2018 - 06:54

Please do give me a good feedback for my service.

Excelchat Expert
18/06/2018 - 06:54

Thanks for your time. Please do come back for any new question. Have a great day ahead!

User
18/06/2018 - 06:55

you too!

Excelchat Expert
18/06/2018 - 06:55

You can now exit this session. Thanks!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*