Spreadsheet: 761383d1640868396-using-time-to-charge-different-prices-percentage-distribution-dbmonkey-
Sheet: Sheet1

Charging event | User ID | CP ID | Start Date | Start Time | End Date | End Time | Total kWh | Cost | Site | PayId | Car | Staff | Error | Excess | Day | Night | Total | Total | % Night | Night | % Day | Day |  | Staff ID
16012785 | 520090 | Fast08 | 2021-12-21 00:00:00 | 02:26:00 | 2021-12-21 00:00:00 | 06:43:00 | 12.462 | 2.73 | 22KW at Co-Op Rushton Mellows | C8D0C104 |  | False |  |  | 0 | 1.37 |  |  |  |  |  |  |  | 101
16012855 | 520092 | Fast20 | 2021-12-21 00:00:00 | 03:17:00 | 2021-12-21 00:00:00 | 11:50:00 | 19.159 | 4.2 | 22KW at Old Shoe Road Car Park | C8D0C101 |  | False |  |  | 2.27 | 0.92 |  |  |  |  |  |  |  | 102
16012794 | 520087 | Fast24 | 2021-12-21 00:00:00 | 03:19:00 | 2021-12-22 00:00:00 | 17:46:00 | 0 | 2 | 22KW at Co-Op Pentford | C8D0C103 | BMW i3 | False |  |  | 0 | 0 |  |  |  |  |  |  |  | 103
16012797 | 520091 | Rapid03 | 2021-12-21 00:00:00 | 14:19:00 | 2021-12-21 00:00:00 | 15:33:00 | 34.9 | 13.75 | 50KW Rapid at The Powershack | 8069C20 |  | False |  |  | 7.33 | 0 |  |  |  |  |  |  |  | 104
16012798 | 520093 | Fast20 | 2021-12-21 00:00:00 | 05:23:00 | 2021-12-22 00:00:00 | 04:35:00 | 19.629 | 2.1 | 22KW at Old Shoe Road Car Park | 8069C19 | Nissan LEAF | False |  |  | 3.02 | 0.58 |  |  |  |  |  |  |  | 105
16012808 | 520100 | Fast18 | 2021-12-21 00:00:00 | 14:31:00 | 2021-12-21 00:00:00 | 14:31:00 | 0 | 0 | 22KW at Little Hampton | C8D0C102 | BMW i3 | False | True |  | 0 | 0 |  |  |  |  |  |  |  | 106
16012846 | 520086 | Rapid05 | 2021-12-21 00:00:00 | 15:04:00 | 2021-12-21 00:00:00 | 15:42:00 | 4.52 | 2 | 50KW Rapid - Tortilla Palace | 8069C18 | BMW i3 | False |  |  | 0.95 | 0 |  |  |  |  |  |  |  | 107
16012862 | 520085 | Fast02 | 2021-12-21 00:00:00 | 15:26:00 | 2021-12-21 00:00:00 | 18:08:00 | 6.998 | 2 | 22KW at Patriotic Street Car Park | 8069C17 | [NAME] | False |  |  | 1.47 | 0 |  |  |  |  |  |  |  | 108
16012878 | 520099 | Fast18 | 2021-12-21 00:00:00 | 15:37:00 | 2021-12-21 00:00:00 | 15:38:00 | 0.04 | 0 | 22KW at Little Hampton | C8D0C100 | BMW i3 | False | True |  | 0 | 0 |  |  |  |  |  |  |  | 199
16012879 | 520090 | Rapid03 | 2021-12-21 00:00:00 | 15:39:00 | 2021-12-21 00:00:00 | 16:42:00 | 10.8 | 2.75 | 50KW Rapid at The Powershack | 8069C16 |  | False |  |  | 2.27 | 0 |  |  |  |  |  |  |  | 519778
16012904 | 520101 | Fast16 | 2021-12-21 00:00:00 | 16:22:00 | 2021-12-21 00:00:00 | 16:32:00 | 0.582 | 2 | 22KW at The Powershack | C8D0C99 | Nissan E-NV200 | False |  |  | 0.12 | 0 |  |  |  |  |  |  |  | 519779
16012910 | 520098 | Fast15 | 2021-12-21 00:00:00 | 16:24:00 | 2021-12-21 00:00:00 | 17:06:00 | 5.018 | 2 | 22KW at Cheshire Homes | 8069C15 | smart fourtwo ed | False |  |  | 1.05 | 0 |  |  |  |  |  |  |  | 519780
16012931 | 519779 | Rapid03 | 2021-12-21 00:00:00 | 16:56:00 | 2021-12-21 00:00:00 | 17:01:00 | 3.3 | 0 | 50KW Rapid at The Powershack | C8D0C98 |  | True |  |  | 0.69 | 0 |  |  |  |  |  |  |  | 519781
16012930 | 520095 | [NAME] | 2021-12-21 00:00:00 | 16:57:00 | 2021-12-21 00:00:00 | 16:57:00 | 0 | 0 | 7KW at [NAME] | 8069C14 | Honda E | False | True |  | 0 | 0 |  |  |  |  |  |  |  | 519782
16012932 | 520094 | [NAME] | 2021-12-21 00:00:00 | 16:57:00 | 2021-12-22 00:00:00 | 06:57:00 | 26.975 | 4.37 | 7KW at [NAME] | [NAME] | Honda E | False |  |  | 2.85 | 1.47 |  |  |  |  |  |  |  | 520071
16012936 | 520097 | Fast10 | 2021-12-21 00:00:00 | 17:00:00 | 2021-12-22 00:00:00 | 09:32:00 | 10.602 |  | 22Kw at Hope Airport | 8069C13 |  | False |  |  | 1.28 | 0.49 |  |  |  |  |  |  |  | 520072
16012952 | 520096 | Fast10 | 2021-12-21 00:00:00 | 17:30:00 | 2021-12-22 00:00:00 | 06:05:00 | 13.856 | 2.24 | 22Kw at Hope Airport | C8D0C96 | BMW i3 | False |  |  | 1.5 | 0.74 |  |  |  |  |  |  |  | 520073
16012963 | 520089 | Fast08 | 2021-12-21 00:00:00 | 17:51:00 | 2021-12-21 00:00:00 | 17:51:00 | 0 | 0 | 22KW at Co-Op Rushton Mellows | 8069C12 |  | False | True |  | 0 | 0 |  |  |  |  |  |  |  | 520075
16012964 | 520088 | Fast08 | 2021-12-21 00:00:00 | 17:52:00 | 2021-12-21 00:00:00 | 19:48:00 | 12.682 | 2.73 | 22KW at Co-Op Rushton Mellows | C8D0C95 |  | False |  |  | 2.66 | 0 |  |  |  |  |  |  |  | 520076
16012971 | 520088 | Rapid03 | 2021-12-21 00:00:00 | 03:03:00 | 2021-12-21 00:00:00 | 04:22:00 | 24.207 | 2 | 50KW Rapid at The Powershack | 8069C11 |  | False |  |  | 5.08 | 0 |  |  |  |  |  |  |  | 520077
16012999 | 520092 | Fast06 | 2021-12-21 00:00:00 | 18:52:00 | 2021-12-21 00:00:00 | 21:32:00 | 3.673 | 2 | 22KW at Blue Tooth Garden | C8D0C94 | Toyota RAV4 | False |  |  | 0.77 | 0 |  |  |  |  |  |  |  | 520078
16013015 | 520102 | Rapid05 | 2021-12-21 00:00:00 | 19:19:00 | 2021-12-21 00:00:00 | 21:29:00 | 20.662 | 9.41 | 50KW Rapid - Tortilla Palace | 8069C10 | Renault ZOE | False |  |  | 4.34 | 0 |  |  |  |  |  |  |  | 520079
16013048 | 520093 | Fast06 | 2021-12-21 00:00:00 | 20:10:00 | 2021-12-22 00:00:00 | 08:02:00 | 17.546 | 2.68 | 22KW at Blue Tooth Garden | C8D0C93 | BMW i3 | False |  |  | 1.51 | 1.14 |  |  |  |  |  |  |  | 520080
16013066 | 520087 | Rapid03 | 2021-12-21 00:00:00 | 21:43:00 | 2021-12-21 00:00:00 | 21:49:00 | 3.2 | 2 | 50KW Rapid at The Powershack | 8069C09 | BMW i3 | False |  |  | 0.67 | 0 |  |  |  |  |  |  |  | 520081
16013069 | 520084 | Fast02 | 2021-12-21 00:00:00 | 22:11:00 | 2021-12-22 00:00:00 | 08:56:00 | 17.87 | 2.58 | 22KW at Patriotic Street Car Park | D9A66C94 |  | False |  |  | 1.3 | 1.28 |  |  |  |  |  |  |  | 520082
16013081 | 520091 | Fast01 | 2021-12-21 00:00:00 | 23:20:00 | 2021-12-22 00:00:00 | 06:35:00 | 27.507 | 3.27 | 22KW at M&S Preston Road | 45AB67F4 |  | False |  |  | 0.51 | 2.76 |  |  |  |  |  |  |  | 520083
 | 2 Days |  |  |  | Same Day Split
 |  |  | 0.11 |  |  |  | 0.11 |  |  | 06:59:59.942000
 |  |  | 0.21 |  |  |  | 0.21 |  |  | 23:59:59.983000 |  | Staff = ISNUMBER(MATCH($B2,$AB$2:$AB$122,0))
 |  |  |  |  |  |  |  |  |  |  |  | Error =IF(E2 = G2,IF(H2 - F2 <0.0015,"Error",""),"")
 |  |  |  |  |  |  |  |  |  |  |  | Rapid = IF(ISNUMBER(SEARCH("Rapid",N2,1)),IF(H2 - F2 > 0.045,5,),0)
 | All Day |  |  |  | All Night
 |  |  |  |  |  | 1 | 0.11 |  |  |  |  | Notes
 |  | 1 | 0.21 |  |  |  |  |  |  |  |  | Minimum £2
 |  |  |  |  |  |  |  |  |  |  |  | If Rapid > 1 hour £5 additional charge (Allow 1 hr 5 min)
 |  |  |  |  |  |  |  |  |  |  |  | [NAME] 07:00:00 to 23:59:59  at 21p per hour
 |  |  |  |  |  |  |  |  |  |  |  | Rapid at 21p per KW hour all times
 |  |  |  |  |  |  |  |  |  |  |  | night time midnight to 06:59:59 at 11p per hour
 |  |  |  |  |  |  |  |  |  |  |  | Allow 3 mins to make an error and stop for no charge
 | IF(OR(M2=TRUE,N2=TRUE),0, IF(ISNUMBER(SEARCH("Rapid",J2,1)),IF(G2 - E2 > 0.045,5+(H2*0.21),IF(H2*0.21<2,2,H2*0.21)),IF(D2<F2,IF(((H2*0.21)*IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2)))/(IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))+IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2)))))+(H2*0.11) *IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))/(IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))+IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2))))<2,2,((H2*0.21)*IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2)))/(IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))+IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2)))))+(H2*0.11) *IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))/(IF(E2<0.291666,IF(G2>0.291666,0.291666+(0.291666-E2),G2+(0.291666-E2)),IF(G2>0.291666,0.291666,G2))+IF(E2<0.291666,IF(G2>0.291666,(1-0.291666)+(G2-0.291666),(1-0.291666)),IF(G2>0.291666,(1-E2)+(G2-0.291666),(1-E2))))),IF(AND(E2>0.291667,G2>0.291667),IF(H2*0.21<2,2,H2*0.21),IF(AND(E2<0.291667,G2<0.291667),IF(H2*0.11<2,2,H2*0.11),IF(AND(E2<0.291667, G2 >0.291667),IF( (H2*0.11)*((0.291666-E2)/((0.291666-E2)+(G2-0.291666)))+(H2*0.21)*((G2-0.291666)/((0.291666-E2)+(G2-0.291666)))<2,0, (H2*0.11)*((0.291666-E2)/((0.291666-E2)+(G2-0.291666)))+(H2*0.21)*((G2-0.291666)/((0.291666-E2)+(G2-0.291666)))),"error"))))))