Excel to do time calculations for delayed timer

I've got a very practical and real world problem that I'm trying to solve using Excel but I just can't figure out how to do it. I'm hoping that some kind person out there is able to help me out on how to solve this using Excel Formulas. I'll be able to see how someone has solved it so it will be a good learning exercise for me, and hopefully it will provide a little fun exercise for whomever is providing the solution :-D

Summary:
I'd like to create some Excel formulas that can work out the best time for me to run my washing machine during my off-peak cheaper electricity. I need to do some Excel time arithmetic to do this. The thing that makes it tricky is that the duration of the washing machine programme, the start and end times of the off-peak electricity and the current time all affect the calculation.

The washing machine:
It has a number of different washing programmes each with their own duration ranging from 20 minutes to 2 hours 45 minutes (I believe 2 hours 45 minutes is the longest). I can set a timer delay for the programme, pressing the timer delay for the first time will round up the programme time to the nearest hour (e.g. 20 minutes programme will be rounded up to 1 hour and a 2 hour 45 minutes programme will round up to 3 hours). The time displayed on the delayed timer will be the finish time for the cycle. Therefore, on a 2 hour 45 minute cycle, if I press the timer delay once, then the delay timer will show 3 hours. The washing machine will start in 15 minutes and will be complete in 3 hours. Each subsequent press of the timer delay will add an additional hour of delay.

I'm guessing this is how most washing machine delay timers work but I've described it in full just in case.

Off-peak electricity:
My electricity has a 7 hour off-peak period (economy 7 tariff), this is usually 12.30am to 7.30am but I've been with some providers that have been different. Additionally, this time is at GMT, so when we move to daylight savings time, I need to add an extra hour onto this. However, there are some electricity tariffs that are different to the 7 hour period so something that will allow me to configure the start and end times for the cheap rate will be good.

What to calculate:
I'd like to be able to input the following:

1. The programme duration
2. Off-peak start time
3. Off-peak end time
4. Time preference (this is explained below)

Time preference will be one of the following:

1. Earliest start with immediate set - i.e. I want to set the timer now so that the start time occurs at the first opportunity in the off-peak window.
2. Earliest start with delayed set - i.e. I'm happy to wait up to an hour to set the timer so that the start time occurs at the very instant the off-peak window starts.
3. Latest finish with immediate set - similar to 1. but finishing at the last opportunity within the off-peak window.
4. Latest finish with delayed set - similar to 2. but finishing at the last opportunity within the off-peak window.

What to calculate:

1. At what time should I set the timer (if I'm doing a delayed set)
2. How many presses of the delayed timer should I enter (i.e. how many hours should the display be set to)
3. The calculated start time
4. The calculated finish time

I've been trying to do the above calculations in my head but I've often got it wrong. I'm hoping that some kind person is able to help me so that I can easily calculate things.

Thanks in advance

Hi,
In order to understand you better - can you please provide a concrete example of your washing machine activation, with the data you provide manually to trigger the calculated data?

Thanks.

Originally Posted by belinda200

Hi,
In order to understand you better - can you please provide a concrete example of your washing machine activation, with the data you provide manually to trigger the calculated data?

Thanks.

So an example would be:

Assume the following values:
Programme duration is: 2 hours 45 mins
Current time is: 6.50pm
Off-peak starts at: 12.30am
Off-peak ends at: 7.30am

Examples based on above:

• If I started the programme immediately, then it would start at: 6.50pm and finish 2 hours 45 minutes later (i.e. finish at 9.35pm).
• If I pressed the delayed timer once, then the washing machine will delay the start such that it will finish in 3 hours (because 3 hours is the nearest hour when rounding up the programme duration). This will start the programme at 7.05pm and it will finish at 9.50pm.
• If I pressed the delayed timer twice, then the washing machine will delay the start such that it will finish in 4 hours. This will start the programme at 8.05pm and it will finish at 10.50pm.

Therefore, given the type of time preference, the expected output should be:

Time Preference	Wait until	Timer Delay Button Presses	Washing machine display finish in	Programme Start	Programme Finish
Earliest start with immediate set	Immediate	7	9	1.05am	3.50am
Earliest start with delayed set	25 minutes	6	8	12.30am	3.15am
Latest finish with immediate set	Immediate	10	12	4.05am	6.50am
Latest finish with delayed set	40 minutes	10	12	4.45am	7.30am

Hope this helps, many thanks.

Hi,
Thanks for the explanations.

So - I attached an excel file with your table and formulas to automate the results.
B2:E2 are the variable parameters you need to fill in manually.


Then you have 'Button Press calculation' at C column.

Assuming that B11 & B9 are invariably '40 minutes' & '25 minutes' respectively please see the formulas below and attached:

Earliest start with immediate set:
C8 = ROUND(($D$2-$C$2)*24,0)+IF(MINUTE($B$2)<>0,1,0)

Earliest start with delayed set:
c9 =ROUND(($D$2-($C$2+"00:25"))*24,0)+IF(MINUTE($B$2)<>0,1,0)

Latest finish with immediate set:
C10 =ROUND((($E$2-$B$2)-C2)*24,0)

Latest finish with delayed set:
C11 =IF((E2-B2)>((C2+"00:40")+((ROUND((($E$2-$B$2)-(C2+"00:40"))*24,0))/24)),ROUND((($E$2-$B$2)-(C2+"00:40"))*24,0)+1,ROUND((($E$2-$B$2)-(C2+"00:40"))*24,0))


Really hope this helps, I admit I struggled to understand the agenda, so there's a chance that there are holes in the formula, I suggest you test it with few scenarios to verify it functions propely.

* I didnt understand the logic behind the 'Washing machine display finish in' - so I left it as is.

That's amazing, thank you. I'll take a look at it :-D