Forum Discussion
Excel TIme Formula - calculate from specific Range
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?
HISergeiBaklan ,
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
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