Results 1 to 3 of 3

Calculating Peak and Off-Peak Hours (Electricity Usage)

Threaded View

  1. #1
    Registered User
    Join Date
    10-11-2022
    Location
    Auckland, New Zealand
    MS-Off Ver
    2020
    Posts
    1

    Calculating Peak and Off-Peak Hours (Electricity Usage)

    I've tried various methods and formulas but can't seem to get this right, mostly due to peak hours being on two different time slots.

    My provider charges different rates based on time and day.
    Peak Weekday Hours: 07:00-11:00, 17:00-21:00 >>>> (all other times are off-peak)
    Off-Peak Hours (Weekday): 00:00-10:59, 11:01-17:00, 21:01-11:59
    Weekends: Off-Peak

    Please help me with a formula that checks if a date falls on a weekday and time is within peak hours, then return 1/true/P, otherwise, leave 0/false/OP

    Closest formula I've come up with:
    =IF(OR(I2<$M$2,I2>=$M$3),"P",IF(AND(I2>=$M$3,I2<$M$4),"AM",IF(AND(I2>=$M$5,I2<$M$6),"OP","")))
    =IF(AND(WEEKDAY(H2,2)<=5,WEEKDAY(H2,2)>=1,H2-INT(H2)>=0.25,H2-INT(H2)<=0.875),"P","OP")
    Attached Files Attached Files
    Last edited by kuzzy00; 10-11-2022 at 06:40 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 05-01-2015, 12:02 PM
  2. Electricity Calculations based on Peak and Off Peak Rate
    By mrwrighty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 07:43 AM
  3. Calculating on-peak and off-peak hours
    By excelas88 in forum Excel General
    Replies: 2
    Last Post: 05-22-2014, 03:57 PM
  4. Counting the peak & non peak hours between two dates?
    By zeroprobe in forum Excel General
    Replies: 3
    Last Post: 12-09-2013, 06:53 AM
  5. Calculating On-peak and Off-peak Hours
    By helloganesh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2013, 08:10 PM
  6. Replies: 1
    Last Post: 03-13-2006, 07:27 PM
  7. How to Calculate Peak and Off Peak Hours
    By nikolasp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2005, 12:00 AM

Tags for this Thread

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