+ Reply to Thread
Results 1 to 5 of 5

Excel to do time calculations for delayed timer

  1. #1
    Registered User
    Join Date
    06-30-2017
    Location
    United Kingdom
    MS-Off Ver
    Office 2016
    Posts
    6

    Question 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

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    616

    Re: Excel to do time calculations for delayed timer

    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.

  3. #3
    Registered User
    Join Date
    06-30-2017
    Location
    United Kingdom
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Excel to do time calculations for delayed timer

    Quote Originally Posted by belinda200 View Post
    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.

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    616

    Re: Excel to do time calculations for delayed timer

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-30-2017
    Location
    United Kingdom
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Excel to do time calculations for delayed timer

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. interest calculations for delayed payments made in parts.
    By rakeshshethia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2016, 12:35 PM
  2. Need Help Please: Excel Click Timer to time each Work/Task
    By jsioson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2013, 01:49 AM
  3. Time delayed macro ??
    By SportyJim1979 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2012, 10:59 AM
  4. Time Delayed Pause Break
    By sajeel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2010, 01:14 PM
  5. Time delayed macro
    By phil2006 in forum Excel General
    Replies: 2
    Last Post: 05-09-2008, 07:51 PM
  6. Time delayed macro
    By phil2006 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2008, 09:02 AM
  7. Replies: 1
    Last Post: 03-10-2006, 08:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1