Excel TIme Formula - calculate from specific Range

[StardrifterIV]

Hi all,

I am having trouble wrapping my head around this: Id like to calculate and display the hours between midnight and 7am in a third cell from a range entered into 2 different Cells. E.g. C3=21:00; D3= 03:00, id like the 3rd Cell e.g. E3= 3hrs (midnight to 3am). This needs to be dynamic and understand that the constraints are midnight to 7am and to only calculate the hours as such. Time range (in C3/D3) may be any time in a 24hr period.

Hope this makes sense, hope someone can help.

Thanks so much

Chris

[#2]

@StardrifterIV 

That could be

we assume if value in D is more than value in C that's the same day. Otherwise how Excel knows D4 is the same day as C4 or next one?

[#3]

HI@Sergei Baklan ,

I very much appreciate you having a look at this. 

... here  in lies the rub, I need to isolate only the hours from Midnight to 3 am from the 9pm - 3am data range. 

I am going down the path of (If) as it is the only way I see to ask excel to look specifically for times between midnight and 7am, however my knowledge of excel is not poor, it is certainly a struggle to get through this one with the conceptualisation and the syntax as well

I am thinking 'If D = 00:00 thru 07:00 then sum(00:00:00 + D3)' something to that effect would result in the 3hours that I am looking for in the example. I dont know the excel Syntax and functions well enough, and have spent hours trolling through the various Excel functions to find the concept, without even knowing if im barking up the right tree

Thanks again for your response. Any Guidance is most appreciated!

T

[#4]

@StardrifterIV 

Syntax for  'If D = 00:00 thru 07:00 then sum(00:00:00 + D3)' will be

=IF( D3<=7/24,D3, MOD(D3-C3,1) )

 assuming we use previous formula for another time. Entire logic is not clear for me, what it shall be if D3 is 07:01 and C3 is 23:59, etc.

To explain the formula, days in Excel are integers where 1 is Jan 01, 1900. Time is decimal part of the number. Thus in calculations one day is equal to 1 and one hour to 1/24.

Thus 2021-01-14 13:00 is human friendly representation of datetime, actually that is 44210+13/24 or 44210.54167