Calculate hours falling inside a time interval

I am trying to find the correct excel formula to use: I have 2 cells that contain a start time [cell E2] and an end time [cell F2]. I want to calculate how many hours there are between the timeframe of 00:00 and 08:00 based on those start and end times. I have found a formula that works:

=IF((E2*24)<8,8-(E2*24),IF((F2*24)<8,F2*24,""))

However the formula will be exported into excel using this identical formula across every row, therefore I am unable to specify rows only columns to calculate each row of the worksheet correctly.
I then found one formula that works to calculate the hours after 00:00 only, referencing only the end time time in column F:

=SUM(INDIRECT(ADDRESS(ROW(),6))*24)

and one formula to calculate hours before 08:00, referencing only the start time in column E:

=SUM(8-(INDIRECT(ADDRESS(ROW(),5))*24))

I would like to find a way to combine the last two formulas to achieve the same result from the first formula I used when I specified the cells to be used. Can anyone help? Any suggestions would be appreciated in achieving this or any other alternative solution.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. If I understood what you are trying to do, I'd have done it for you ...)

EDIT: Glenn did it for you.

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new user of this site, on this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

ALSO...



A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

Disregard.