LinkBack URL
About LinkBacks
Hi, experts,
I've been searching formula for missing hours within date and hour range, but the result is not the missing within range
Is it possible to do this with formula the way given in the attached sample file?
Thanks.
NB: sorry for my english
Hi, experts,
I've been searching formula for missing hours within date and hour range, but the result is not the missing within range
Is it possible to do this with formula the way given in the attached sample file?
Thanks.
NB: sorry for my english
Welcome to the forum.
Please explain in detail the results that yo uare expecting how do you know that these hours are 'missing'?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the reply.
We know how the hours are missing is by time range from
8 to 17
Or
8 to 12 and 13 to 17.
Ex:
We input
10 to 12 and
13 to 15
The missing hours is 8 to 10 and 15 to 17 because it still missing 3 hours from that.
Usually, the formula only count total of the missing hours, not showing when.
Hope it can clears the confusion 🙇*♂️
Sorry for my english.
No, not really - sorry. HOW do we know that the hours are missing? What is the logic?
1. E3: Why is your expected answer 11:00 - 12:00 and not 10:00 - 12:00???
2. What would the expected answer be if B3 was 09:00 and the other values are unchanged?
3. Can you have times that cross the two shifts... e.g. start 09:00 finish 17:00?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
AliGW
From range 8 to 17.
The logic is if we don't input full 8 to 17 or 8 to 12 and 13 to 17, it will be missing hours.
Glenn Kennedy
1. Sorry my bad, 10:00 - 12:00 is the correct one
2. The answer is 08:00 - 09:00 and 10:00 - 12:00
3. It's ok even the answer is only 08:00 - 09:00
Your sample was VERY small... so this may not cover everything. For now, try this:
Formula:
Please Login or Register to view this content.
copied down.
Glenn Kennedy,
IT WORKS, THANK YOU
Changed a little bit for E4 by removing IF OR formula, and it works like E3.
Really, thank you so much for helping me.
You're welcome. Thanks for letting us know that you got an answer.
Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks