Electrical Time of Use Calculations

I use Excel quite a bit but am not the greatest at formulas. I'm inserting an image of what my data looks like and I'll describe what I want to accomplish.

First, I need Excel to look at the day of the week and time of day to determine if its an on/off peak day and an on/off peak time of day. 11 p.m. to 7 a.m., Monday through Saturday is always off peak, and then Sunday all day is off peak. Also, we have 5 or 6 holidays (I'd have to look at rate schedule to see what they are) that are off peak. What do I need to do to calculate this on ever cell, so at the beginning of each month, I'm not manually moving my formulas around to the correct days of the week?

Sorry if I'm not making my questions clear. Hopefully someone is familiar with what I'm trying to do. I tried to search the forums for something like this, but not sure what to look for. Please point me in the right direction if its already answered.

Thanks in advance!! Below is the image!

Column D and E sum up the KWH (column B) down to that point and then I add all those up at the bottom. D and E have those formulas at the end of each on/off peak. They add up the previous time period's KWH. If it would be better for me to attach the working document, I can create a non-sensitive copy.

TOU Data Calculation.jpg

That is exactly what I was trying to do. I had the right idea, I was just putting the "AND" portion of my formula in the wrong place. Also, I was using a round about way to get the day of week and time out of the timestamp, and so you taught me how to extract the text and format. Was not sure how to use that either!

Thanks you SO very much!!!!

Originally Posted by Melvinrobb

Here is a solution without having the day and time extracted (based solely on cell A2):

Formula:

Please Login or Register  to view this content.

I like that option just because it would save me a couple of columns, but I have to include the 23:00 increment in my On Peak because the data at 23:00 is actually used in the 15 minute period before that! And I think I would be eliminating the 7:00 through 7:45 increments with that formula also. Correct me if I'm missing something here.

Thanks!

One more question in all this... Does Excel recognize or acknowledge holidays? And can I specify which ones I want to include in the Off Peak data?

That might! Will that return all 23:00 to 23:45 increments tho? Or does it look beyond the hour even tho it doesn't return the :15, :30, etc.?

Thanks!

You guys rock!!! That pretty much takes all the human error away now! Very much appreciated!!!!!

Originally Posted by Harribone

For days you want to specify as a holiday/off peak you will need to create a new sheet (Called Holidays for this example). In column A enter the dates that you have eg. 03/01/2013.
Make sure you set the column format to TEXT so this way will work.

=IF(IFERROR(VLOOKUP(TEXT(A2,"dd/mm/yyyy"),Holidays!A:A,1,0),0)=TEXT(A2,"dd/mm/yyyy"),"OFF",IF(OR(WEEKDAY(A2)=1,HOUR(A2)<7,HOUR(A2)>=23),"OFF","ON"))

This is Melvinrobb's formula with a bit extra to check if the date is in your list.

Another question about the IFERROR part of that equation. I can't seem to wrap my mind around what that does. I'm assuming it's saying that if the VLOOKUP on the Holidays tab causes an error, it just returns a 0, but even if that's correct, why do we need that? I tried removing the IFERROR part of the formula and it just creates a !NAME# error (or w/e it is).

Thanks.