Dynamic modelling with the 24 hour time format

Hi guys,

I have been struggling with a particular problem in Excel for a while, come here hoping someone in the forum might be able to offer a helping hand!

I am creating a tool in Excel to model the charging of an Electric Vehicle. In the example picture attached; a Nissan Leaf battery is completely empty, and for the chosen charge point type, takes 10 hours to completely recharge at an assumed 80% batter efficiency (3 * 10 * 0.8 = 24). So there is a demand of "3" over a 10 hour period, with the start time specified by the scroll bar and the end time calculated as appropriate.

I have found one simple way of modelling (almost) the result format I want - as shown in Columns E and F - but what I really want is highlighted in yellow with red text, a chronological time display with the charging demand alongside. But I am struggling to achieve this for specifically overnight charging e.g. from 9 p.m. - 6 a.m. as shown.

I want to link this charging demand to a supply of renewable energy, but crucially I only have average values for 12 a.m. - 11 p.m. over one day. Therefore - whether I start charging at 5 a.m. and finish at 10 a.m. or start charging at 10 p.m. and finish at 5 a.m. - I want Excel to treat both 5 a.m.'s as the same day (i.e. not try and move on a day which seems to be the main cause of problem within IF limits I have tried to apply, as you can see the attempt of Column I fails).

(I should say my initial plan was not to specify start and end times using the scroll bar at all, but to have a block equaling the demand of "3" in this case which could be moved up and down from 12 a.m. - 11 p.m. with its height (or number of rows) decided by the "Full charge (hours)" - I believe this may be possible but I cannot come close to teaching myself how to do it!)

Excel Forum Post.png

I hope what is written above gives a reasonable description of my problem, if not I will clarify further related to any specific questions. If anyone can provide any level of help on this matter whatsoever, I would really appreciate it.

Thanks in advance,

Calum

I have attached the workbook as shg suggested, thanks for the advice!

The Tool.xlsx

That's brilliant shg, thanks for your help!

You have no idea how much time that is going to save me.

doverman, shg's equation did require a slight change to:

=(($B$17 <= 1) * ((G2 > $B$16 - "0:0:1") * (G2 <= $B$17 + "0:0:1")) + ($B$17 > 1) * ((G2 <= $B$17 - "23:59:59") + (G2 >= $B$16 - "0:0:1"))) * $C$5

I think this was mostly my fault due to a slightly unclear description.