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.
