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