Calculate how many hours in a time range falls within a predefined Time Range, spanning midnight

[ito69]

Let the predefined time range be

Battery_Time :
From -> 05:00 PM (lower)
To -> 06:00 AM (upper)
Total_Hrs = 13 hrs.

Using ONLY excel formula, I want to be able to know if the following ranges/shifts fall fully or partially within the Battery_Time range defined above, and if so, how many hours:

Time	Start	End	Total_Hrs	Battery_Hrs
T1	5:00 PM	11:00 PM	6	6
T2	6:00 PM	5:00 AM	11	11
T3	11:59 PM	2:00 AM	24	13
T4	12:00 AM	11:59 PM	24	13
T5	10:00 AM	2:00 PM	4	0
T6	6:00 PM	7:00 AM	13	12
T7	1:00 AM	7:00 AM	6	5
T8	2:00 PM	8:00 PM	6	3

This is for a Photovoltaic project; I want to be able to verify the percentage of my electrical loads that I need battery storage.



Thank you for your time

 

[ito69]

This is my formula , T3 & T4 do not work:

=MAX(0,[@[Total_Hrs]]-(IF([@Start]<lower,MOD(lower-[@Start],1)*24,0) + IF(MOD([@End],1)>0.5,IF([@End]<lower,MOD([@End]-[@Start],1)*24,0),IF([@End]>upper,MOD([@End]-upper,1)*24,0))))

 

[ito69]

Let the predefined time range be

Battery_Time :
From -> 05:00 PM (lower)
To -> 06:00 AM (upper)
Total_Hrs = 13 hrs.

Using ONLY excel formula, I want to be able to know if the following ranges/shifts fall fully or partially within the Battery_Time range defined above, and if so, how many hours:

Time	Start	End	Total_Hrs	Battery_Hrs
T1	5:00 PM	11:00 PM	6	6
T2	6:00 PM	5:00 AM	11	11
T3	11:59 PM	2:00 AM	24	13
T4	12:00 AM	11:59 PM	24	13
T5	10:00 AM	2:00 PM	4	0
T6	6:00 PM	7:00 AM	13	12
T7	1:00 AM	7:00 AM	6	5
T8	2:00 PM	8:00 PM	6	3

This is for a Photovoltaic project; I want to be able to verify the percentage of my electrical loads that I need battery storage.



Thank you for your time

Please disregard T3

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Calculate how many hours in a time range falls within a predef Range, spanning midnight

Let the predefined time range be Battery_Time : From -> 05:00 PM (lower) To -> 06:00 AM (upper) Total_Hrs = 13 hrs. Using ONLY excel formula, I want to be able to know if the following ranges/shifts fall fully or partially within the Battery_Time range defined above, and if so...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.