Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.