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:
- The programme duration
- Off-peak start time
- Off-peak end time
- Time preference (this is explained below)
Time preference will be one of the following:
- 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.
- 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.
- Latest finish with immediate set - similar to 1. but finishing at the last opportunity within the off-peak window.
- Latest finish with delayed set - similar to 2. but finishing at the last opportunity within the off-peak window.
What to calculate:
- At what time should I set the timer (if I'm doing a delayed set)
- How many presses of the delayed timer should I enter (i.e. how many hours should the display be set to)
- The calculated start time
- 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
Bookmarks