I need to calculate, in decimal hours, how many of the hours from a specified range, fall within another specified range. For example, for each of the ranges below, I need to know how many decimal hours fall between 07:00 and 17:00:
07:00 to 19:30
09:30 to 16:30
07:00 to 14:00
09:30 to 19:45
Please help!
Last edited by Alaird; 08-03-2021 at 06:40 AM.
Welcome to the forum.
There is no MS Office 10 - which version do you have? Please update your profile. Thanks.
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.
My initial response is this:
=C2-B2
If that's not it, then please manually populate the results column with what you want to see and post your sample workbook again.
You can use this formula in D2:
=(MIN(TIME(17,0,0),C2)-MAX(TIME(7,0,0),B2))*24
Format as General or Number, then copy down as required.
Hope this helps.
Pete
edit: below is just a variant of Pete's approach above...
I think it would be along lines of:
=MIN("17:00",C2)-MAX("07:00",B2)
copied down
if you can have start / end times that cross midnight let us know
The OP wants it as decimal hours, rather than time format.
Pete
Fantastic, that works, thank you!!!
Please update your profile to MS365.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks