Formula to split date and time and work out energy usage trend

S

Sundance_Kid

Board Regular
Joined
Sep 2, 2017
Messages
155
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have downloaded an excel file with a history of our electricity usage and I want to see based on my usage is there a better plan I should sign up to.

So, I want to do the following.

A) Separate the date and time so I can get an idea of monthly usage throughout the year, so I will need to split out the date from the time.
B) Split out the times and divide the timings into 4 categories depending on their time:
1) 8am to 4.59pm - Day Rate
2) 7pm to 10.59pm - Day Rate
3) 11pm to 07.59am - Night Rate
4) 5pm - 6.59pm - Peak Rate

File attached.

Thank you.

Excel_ElectricityUsageFormulaHelp.xlsx
BC
2Read ValueRead Date and End Time
30.1126/05/2024 23:30
40.13226/05/2024 23:00
50.2426/05/2024 22:30
60.45626/05/2024 22:00
70.11626/05/2024 21:30
80.1626/05/2024 21:00
92.01826/05/2024 20:30
100.74226/05/2024 20:00
110.05426/05/2024 19:30
120.0626/05/2024 19:00
130.07426/05/2024 18:30
140.0426/05/2024 18:00
150.07626/05/2024 17:30
160.05426/05/2024 17:00
170.0626/05/2024 16:30
180.07426/05/2024 16:00
190.0426/05/2024 15:30
200.07626/05/2024 15:00
210.05426/05/2024 14:30
220.0626/05/2024 14:00
230.07426/05/2024 13:30
240.0426/05/2024 13:00
250.07426/05/2024 12:30
260.05426/05/2024 12:00
270.0626/05/2024 11:30
280.07426/05/2024 11:00
290.0426/05/2024 10:30
300.07426/05/2024 10:00
310.05426/05/2024 09:30
320.0626/05/2024 09:00
330.07426/05/2024 08:30
340.0426/05/2024 08:00
350.07626/05/2024 07:30
360.05426/05/2024 07:00
370.0626/05/2024 06:30
380.07626/05/2024 06:00
390.0426/05/2024 05:30
400.07626/05/2024 05:00
410.05426/05/2024 04:30
420.06226/05/2024 04:00
430.07426/05/2024 03:30
440.0426/05/2024 03:00
450.07626/05/2024 02:30
460.05426/05/2024 02:00
470.06426/05/2024 01:30
480.07626/05/2024 01:00
490.0426/05/2024 00:30
500.07626/05/2024 00:00
510.05225/05/2024 23:30
520.06225/05/2024 23:00
530.07625/05/2024 22:30
540.0425/05/2024 22:00
550.07625/05/2024 21:30
560.05225/05/2024 21:00
570.06225/05/2024 20:30
580.7825/05/2024 20:00
590.57825/05/2024 19:30
600.07625/05/2024 19:00
610.05225/05/2024 18:30
620.06425/05/2024 18:00
630.07625/05/2024 17:30
640.0425/05/2024 17:00
650.07625/05/2024 16:30
660.05225/05/2024 16:00
670.06425/05/2024 15:30
680.07625/05/2024 15:00
690.03825/05/2024 14:30
700.07625/05/2024 14:00
710.0525/05/2024 13:30
720.06425/05/2024 13:00
730.07625/05/2024 12:30
740.03825/05/2024 12:00
750.07625/05/2024 11:30
760.0525/05/2024 11:00
770.06425/05/2024 10:30
780.07425/05/2024 10:00
790.0425/05/2024 09:30
800.07625/05/2024 09:00
810.0525/05/2024 08:30
820.06425/05/2024 08:00
830.07625/05/2024 07:30
840.0425/05/2024 07:00
850.07625/05/2024 06:30
860.0525/05/2024 06:00
870.06625/05/2024 05:30
880.07425/05/2024 05:00
890.0425/05/2024 04:30
900.07625/05/2024 04:00
910.05225/05/2024 03:30
920.06425/05/2024 03:00
930.07625/05/2024 02:30
940.0425/05/2024 02:00
950.07625/05/2024 01:30
960.0525/05/2024 01:00
970.06625/05/2024 00:30
980.07825/05/2024 00:00
990.0424/05/2024 23:30
1000.07424/05/2024 23:00
1010.04824/05/2024 22:30
1020.06824/05/2024 22:00
1030.07624/05/2024 21:30
1040.0424/05/2024 21:00
1050.08224/05/2024 20:30
1060.05824/05/2024 20:00
1071.03224/05/2024 19:30
1081.74824/05/2024 19:00
1090.32224/05/2024 18:30
1100.21624/05/2024 18:00
1110.06624/05/2024 17:30
1120.26424/05/2024 17:00
1130.0924/05/2024 16:30
1140.11224/05/2024 16:00
1150.84424/05/2024 15:30
1161.62424/05/2024 15:00
1170.13224/05/2024 14:30
1180.11824/05/2024 14:00
1190.07824/05/2024 13:30
1200.08624/05/2024 13:00
1210.15424/05/2024 12:30
1220.0924/05/2024 12:00
1230.0924/05/2024 11:30
1240.07824/05/2024 11:00
1250.23224/05/2024 10:30
1260.38624/05/2024 10:00
1270.05224/05/2024 09:30
1280.07824/05/2024 09:00
1290.04424/05/2024 08:30
1300.07624/05/2024 08:00
1310.06624/05/2024 07:30
1320.0624/05/2024 07:00
1330.0824/05/2024 06:30
1340.0424/05/2024 06:00
1350.07624/05/2024 05:30
1360.06624/05/2024 05:00
1370.05224/05/2024 04:30
1380.07824/05/2024 04:00
1390.0424/05/2024 03:30
1400.07624/05/2024 03:00
1410.06424/05/2024 02:30
1420.05424/05/2024 02:00
1430.08224/05/2024 01:30
1440.05824/05/2024 01:00
1450.13224/05/2024 00:30
1460.26224/05/2024 00:00
1472.04223/05/2024 23:30
1481.62823/05/2024 23:00
1491.0923/05/2024 22:30
1501.47223/05/2024 22:00
1510.25423/05/2024 21:30
1520.14423/05/2024 21:00
1530.2323/05/2024 20:30
1540.03823/05/2024 20:00
1550.07823/05/2024 19:30
1560.06423/05/2024 19:00
1570.05423/05/2024 18:30
1580.07823/05/2024 18:00
1590.03823/05/2024 17:30
1600.07823/05/2024 17:00
1610.06423/05/2024 16:30
1620.05423/05/2024 16:00
1630.07823/05/2024 15:30
1640.0423/05/2024 15:00
1650.07823/05/2024 14:30
1660.06423/05/2024 14:00
1670.05423/05/2024 13:30
1680.07823/05/2024 13:00
1690.03823/05/2024 12:30
1700.0823/05/2024 12:00
1710.06223/05/2024 11:30
1720.05623/05/2024 11:00
1730.07823/05/2024 10:30
1740.0423/05/2024 10:00
1750.07623/05/2024 09:30
1760.06423/05/2024 09:00
1770.05623/05/2024 08:30
1780.08223/05/2024 08:00
1790.04223/05/2024 07:30
1800.08423/05/2024 07:00
1810.07823/05/2024 06:30
1820.15223/05/2024 06:00
1830.08623/05/2024 05:30
1840.04423/05/2024 05:00
1850.08223/05/2024 04:30
1860.0723/05/2024 04:00
1870.05823/05/2024 03:30
1880.08823/05/2024 03:00
1890.04623/05/2024 02:30
1900.08423/05/2024 02:00
1910.07823/05/2024 01:30
1920.05823/05/2024 01:00
1930.23823/05/2024 00:30
1941.52823/05/2024 00:00
Sheet1
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Try:
Book1
BCDEFGHIJ
1Lookup table
2Read ValueRead Date and End TimeDateTimeCategoryStartEndCategory
30.115/26/52024 23:30:005/26/202411:30:00 PMNight Rate8:00 AM4:59 PMDay Rate
40.1325/26/52024 23:00:005/26/202411:00:00 PMDay Rate5:00 PM6:59 PMPeak Rate
50.245/26/52024 22:30:005/26/202410:30:00 PMDay Rate7:00 PM10:59 PMDay Rate
60.4565/26/52024 22:00:005/26/202410:00:00 PMDay Rate11:00 PM7:59 AMNight Rate
70.1165/26/52024 21:30:005/26/20249:30:00 PMDay Rate
80.165/26/52024 21:00:005/26/20249:00:00 PMDay Rate
92.0185/26/52024 20:30:005/26/20248:30:00 PMDay Rate
100.7425/26/52024 20:00:005/26/20248:00:00 PMDay Rate
110.0545/26/52024 19:30:005/26/20247:30:00 PMDay Rate
120.065/26/52024 19:00:005/26/20247:00:00 PMDay Rate
130.0745/26/52024 18:30:005/26/20246:30:00 PMPeak Rate
140.045/26/52024 18:00:005/26/20246:00:00 PMPeak Rate
150.0765/26/52024 17:30:005/26/20245:30:00 PMPeak Rate
160.0545/26/52024 17:00:005/26/20245:00:00 PMDay Rate
170.065/26/52024 16:30:005/26/20244:30:00 PMDay Rate
180.0745/26/52024 16:00:005/26/20244:00:00 PMDay Rate
190.045/26/52024 15:30:005/26/20243:30:00 PMDay Rate
200.0765/26/52024 15:00:005/26/20243:00:00 PMDay Rate
Sheet4
Cell Formulas
RangeFormula
D3:D194D3=INT(C3:C194)
E3:E194E3=MOD(C3:C194,1)
F3:F194F3=XLOOKUP(E3:E194,H3:H6,J3:J6,,-1)
Dynamic array formulas.
 
Upvote 0
Thanks.

Is there a way to copy the response and paste to excel including formulas and formatting?

It doesn't seem to find a value for the times from 12.30am to say 07.30am.
 
Upvote 0
Sundance_Kid said:
Thanks.

Is there a way to copy the response and paste to excel including formulas and formatting?

It doesn't seem to find a value for the times from 12.30am to say 07.30am.
Click to expand...
The "Copy" symbol at top left of the mini sheet allows you to copy and paste the values and formula exactly. I don't believe it retains the formatting.
 
Upvote 0
Cubist said:
Try:
Book1
BCDEFGHIJ
1Lookup table
2Read ValueRead Date and End TimeDateTimeCategoryStartEndCategory
30.115/26/52024 23:30:005/26/202411:30:00 PMNight Rate8:00 AM4:59 PMDay Rate
40.1325/26/52024 23:00:005/26/202411:00:00 PMDay Rate5:00 PM6:59 PMPeak Rate
50.245/26/52024 22:30:005/26/202410:30:00 PMDay Rate7:00 PM10:59 PMDay Rate
60.4565/26/52024 22:00:005/26/202410:00:00 PMDay Rate11:00 PM7:59 AMNight Rate
70.1165/26/52024 21:30:005/26/20249:30:00 PMDay Rate
80.165/26/52024 21:00:005/26/20249:00:00 PMDay Rate
92.0185/26/52024 20:30:005/26/20248:30:00 PMDay Rate
100.7425/26/52024 20:00:005/26/20248:00:00 PMDay Rate
110.0545/26/52024 19:30:005/26/20247:30:00 PMDay Rate
120.065/26/52024 19:00:005/26/20247:00:00 PMDay Rate
130.0745/26/52024 18:30:005/26/20246:30:00 PMPeak Rate
140.045/26/52024 18:00:005/26/20246:00:00 PMPeak Rate
150.0765/26/52024 17:30:005/26/20245:30:00 PMPeak Rate
160.0545/26/52024 17:00:005/26/20245:00:00 PMDay Rate
170.065/26/52024 16:30:005/26/20244:30:00 PMDay Rate
180.0745/26/52024 16:00:005/26/20244:00:00 PMDay Rate
190.045/26/52024 15:30:005/26/20243:30:00 PMDay Rate
200.0765/26/52024 15:00:005/26/20243:00:00 PMDay Rate
Sheet4
Cell Formulas
RangeFormula
D3:D194D3=INT(C3:C194)
E3:E194E3=MOD(C3:C194,1)
F3:F194F3=XLOOKUP(E3:E194,H3:H6,J3:J6,,-1)
Dynamic array formulas.
Click to expand...
Thanks I was able to copy it, but for some reason the formula is not working for all the time periods.

See highlighted times/rows in the table. Any ideas what the issue may be?

Thank you.
Book2
BCDEFGHIJK
1Read ValueRead Date and End TimeDateTimeCategoryLookup Table
20.1126/05/2024 23:3026/05/202411:30 pmNight RateStartEndCategory
30.13226/05/2024 23:0026/05/202411:00 pmDay Rate8:00 am4:59 pmDay Rate
40.2426/05/2024 22:3026/05/202410:30 pmDay Rate5:00 pm6:59 pmPeak Rate
50.45626/05/2024 22:0026/05/202410:00 pmDay Rate7:00 pm10:59 pmDay Rate
60.11626/05/2024 21:3026/05/20249:30 pmDay Rate11:00 pm7:59 amNight Rate
70.1626/05/2024 21:0026/05/20249:00 pmDay Rate
82.01826/05/2024 20:3026/05/20248:30 pmDay Rate
90.74226/05/2024 20:0026/05/20248:00 pmDay Rate
100.05426/05/2024 19:3026/05/20247:30 pmDay Rate
110.0626/05/2024 19:0026/05/20247:00 pmDay Rate
120.07426/05/2024 18:3026/05/20246:30 pmPeak Rate
130.0426/05/2024 18:0026/05/20246:00 pmPeak Rate
140.07626/05/2024 17:3026/05/20245:30 pmPeak Rate
150.05426/05/2024 17:0026/05/20245:00 pmDay Rate
160.0626/05/2024 16:3026/05/20244:30 pmDay Rate
170.07426/05/2024 16:0026/05/20244:00 pmDay Rate
180.0426/05/2024 15:3026/05/20243:30 pmDay Rate
190.07626/05/2024 15:0026/05/20243:00 pmDay Rate
200.05426/05/2024 14:3026/05/20242:30 pmDay Rate
210.0626/05/2024 14:0026/05/20242:00 pmDay Rate
220.07426/05/2024 13:3026/05/20241:30 pmDay Rate
230.0426/05/2024 13:0026/05/20241:00 pmDay Rate
240.07426/05/2024 12:3026/05/202412:30 pmDay Rate
250.05426/05/2024 12:0026/05/202412:00 pmDay Rate
260.0626/05/2024 11:3026/05/202411:30 amDay Rate
270.07426/05/2024 11:0026/05/202411:00 amDay Rate
280.0426/05/2024 10:3026/05/202410:30 amDay Rate
290.07426/05/2024 10:0026/05/202410:00 amDay Rate
300.05426/05/2024 09:3026/05/20249:30 amDay Rate
310.0626/05/2024 09:0026/05/20249:00 amDay Rate
320.07426/05/2024 08:3026/05/20248:30 amDay Rate
330.0426/05/2024 08:0026/05/20248:00 am#N/A
340.07626/05/2024 07:3026/05/20247:30 am#N/A
350.05426/05/2024 07:0026/05/20247:00 am#N/A
360.0626/05/2024 06:3026/05/20246:30 am#N/A
370.07626/05/2024 06:0026/05/20246:00 am#N/A
380.0426/05/2024 05:3026/05/20245:30 am#N/A
390.07626/05/2024 05:0026/05/20245:00 am#N/A
400.05426/05/2024 04:3026/05/20244:30 am#N/A
410.06226/05/2024 04:0026/05/20244:00 am#N/A
420.07426/05/2024 03:3026/05/20243:30 am#N/A
430.0426/05/2024 03:0026/05/20243:00 am#N/A
440.07626/05/2024 02:3026/05/20242:30 am#N/A
450.05426/05/2024 02:0026/05/20242:00 am#N/A
460.06426/05/2024 01:3026/05/20241:30 am#N/A
470.07626/05/2024 01:0026/05/20241:00 am#N/A
480.0426/05/2024 00:3026/05/202412:30 am#N/A
490.07626/05/2024 00:0026/05/202412:00 am#N/A
500.05225/05/2024 23:3025/05/202411:30 pmNight Rate
510.06225/05/2024 23:0025/05/202411:00 pmDay Rate
520.07625/05/2024 22:3025/05/202410:30 pmDay Rate
530.0425/05/2024 22:0025/05/202410:00 pmDay Rate
540.07625/05/2024 21:3025/05/20249:30 pmDay Rate
550.05225/05/2024 21:0025/05/20249:00 pmDay Rate
560.06225/05/2024 20:3025/05/20248:30 pmDay Rate
570.7825/05/2024 20:0025/05/20248:00 pmDay Rate
580.57825/05/2024 19:3025/05/20247:30 pmDay Rate
590.07625/05/2024 19:0025/05/20247:00 pmDay Rate
600.05225/05/2024 18:3025/05/20246:30 pmPeak Rate
610.06425/05/2024 18:0025/05/20246:00 pmPeak Rate
620.07625/05/2024 17:3025/05/20245:30 pmPeak Rate
Sheet1
Cell Formulas
RangeFormula
D2:D62D2=INT(C2)
E2:E62E2=MOD(C2,1)
F2:F62F2=XLOOKUP(E2,$I$3:$I$6,$K$3:$K$6,,-1)
 
Upvote 0
Try add another row to the lookup table like this.
Book1
IJK
1Lookup Table
2StartEndCategory
38:0016:59Day Rate
417:0018:59Peak Rate
519:0022:59Day Rate
623:0023:59Night Rate
70:007:59Night Rate
Sheet2
 
Upvote 0
Solution
Cubist said:
Try add another row to the lookup table like this.
Book1
IJK
1Lookup Table
2StartEndCategory
38:0016:59Day Rate
417:0018:59Peak Rate
519:0022:59Day Rate
623:0023:59Night Rate
70:007:59Night Rate
Sheet2
Click to expand...
That worked, thank you very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Evaluate date range and return the corresponding month and year
Replies
1
Views
171
Scott Huish
Scott Huish
E
  • Solved
How to scan a column and move adjacent date next to search cell?
Replies
3
Views
67
dreid1011
dreid1011
muhammad susanto
  • Solved
Extract Data to be Date Format (dd-mm-yyyy)
2
Replies
16
Views
267
muhammad susanto
muhammad susanto
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
119
flashgordie
F
M
  • Question Question
SLA Time Calculation
Replies
5
Views
355
felixstraube
F

Forum statistics

Threads
1,224,697
Messages
6,180,403
Members
452,981
Latest member
MarkS1234

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top