formula to calculate based on date

[aelk5579]

Hi,

I am trying to veryify my power bill and have raw meter readings showing kwh consumed in every half hour interval

Is there a formula I can use to calculate the price I should be charged per half hour block?


I am on time of use so i get charged as follows


between 7am and 1pm mon - fri - shoulder rate (0.31/kwh)
between 1pm and 8pm mon - fri - peak rate (0.41/kwh)
all other times (all day weekends) - off peak (0.17/kwh)

example of raw data


[TABLE="width: 258"]
<colgroup><col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;" span="2"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4152;"> <tbody>[TR]
[TD="width: 113, bgcolor: transparent, align: right"]6/04/2018 0:00[/TD]
[TD="width: 113, bgcolor: transparent, align: right"]6/04/2018 0:29[/TD]
[TD="width: 117, bgcolor: transparent, align: right"]1.594kwh
[/TD]
[/TR]
</tbody>[/TABLE]



I have 4500 lines in excel and hoping I dont need to calculate manually per line

Thanks

 

[DRSteele]

Welcome to the forum.

I think this is what you're after. I created sample data for testing purposes.

The Begin and End columns are formatted as Excel Dates and Times. The Begin column is formatted as m/dd/yy h:mm AM/PM and the End column as m/dd/yy h:mm:ss AM/PM. Notice that the End column is superfluous anyway. The Weekday column is fyi; it is simply =A8 and is formatted DDDD.

Copy the formulas downwards.

Book1
	A	B	C	D	E	F
1					0	0.17
2	between 7am and 1pm mon - fri - shoulder rate (0.31/kwh)				6:59:59 AM	0.31
3	between 1pm and 8pm mon - fri - peak rate (0.41/kwh)				12:59:59 PM	0.41
4	all other times (all day weekends) - off peak (0.17/kwh)				7:59:59 PM	0.17
5
6
7	Begin	End	Weekday	Usage (kwh)	Rate ($/kwh)	Cost
8	6/04/18 12:00 AM	6/04/18 12:29:59 AM	Monday	1.594	0.17	$0.2710
9	6/05/18 8:00 AM	6/05/18 8:29:59 AM	Tuesday	2.000	0.31	$0.6200
10	6/06/18 3:00 PM	6/06/18 3:29:59 PM	Wednesday	3.000	0.41	$1.2300
11	6/07/18 8:00 PM	6/07/18 8:29:59 PM	Thursday	5.000	0.17	$0.8500
12	6/08/18 4:00 AM	6/08/18 4:29:59 AM	Friday	6.000	0.17	$1.0200
13	6/09/18 2:00 PM	6/09/18 2:29:59 PM	Saturday	7.000	0.17	$1.1900
Sheet19

Cell Formulas
Range	Formula
B8	=A8+1/48-1/24/60/60
C8	=A8
E8	=IF(OR(WEEKDAY(A8,2)=6,WEEKDAY(A8,2)=7),$F$1,VLOOKUP(MOD(A8,1),$E$1:$F$4,2))
F8	=D8*E8

 

[aelk5579]

Thanks alot for this.. it works perfectly
Just wondering if it would be possible to alter the formula to add one more tarrif.. between 8pm and 10pm weekdays it would be should rate 0.31.. I have modified the table below

between 7am and 1pm mon - fri - shoulder rate (0.31/kwh)

between 1pm and 8pm mon - fri - peak rate (0.41/kwh)

all other times (all day weekends) - off peak (0.17/kwh)

<tbody>
</tbody><thead>
[TH="align: center"]
A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]0.17[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]6:59:59 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]0.31[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]12:59:59 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]0.41[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]7:59:59 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]0.17[/TD]

[TD="align: center"] 5 [/TD]
[TD="align: right"] between 8pm and 10pm mon - fri - shoulder rate (0.31/kwh) [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 7:59:59 PM [/TD]

</tbody>

Thanks again.. you saved me hours and hours of doing this manually