Convert hourly value into two half hourly values

W

Woofy_McWoof_Woof

Board Regular
Joined
Oct 7, 2016
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hi, I have hourly prices for a year (24 hours * 365 days) that I need to convert the same value into half hours. Basically I have a string of price data that I need to duplicate for two half hours as per the below. I have been able to do this via VBA though i would prefer to build the formula into the spreadsheet and lock it down so others can use it.

Book1
ABCD
1TimeStampsPriceTimeStampsPrice
201/06/2022 05:00192.4401/06/2022 05:00192.44
301/06/2022 06:00184.801/06/2022 05:30192.44
401/06/2022 07:00172.8701/06/2022 06:00184.8
501/06/2022 08:00167.301/06/2022 06:30184.8
601/06/2022 09:00161.3601/06/2022 07:00172.87
701/06/2022 10:00155.8301/06/2022 07:30172.87
801/06/2022 08:00167.3
901/06/2022 08:30167.3
1001/06/2022 09:00161.36
1101/06/2022 09:30161.36
1201/06/2022 10:00155.83
1301/06/2022 10:30155.83
Sheet1
 
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Will do, its Excel 365.
 
Upvote 0
Thanks for that.
How about
Fluff.xlsm
ABCD
1TimeStampsPriceTimeStampsPrice
201/06/2022 05:00192.4401/06/2022 05:00192.44
301/06/2022 06:00184.801/06/2022 05:30192.44
401/06/2022 07:00172.8701/06/2022 06:00184.8
501/06/2022 08:00167.301/06/2022 06:30184.8
601/06/2022 09:00161.3601/06/2022 07:00172.87
701/06/2022 10:00155.8301/06/2022 07:30172.87
801/06/2022 08:00167.3
901/06/2022 08:30167.3
1001/06/2022 09:00161.36
1101/06/2022 09:30161.36
1201/06/2022 10:00155.83
1301/06/2022 10:30155.83
14
Main
Cell Formulas
RangeFormula
C2:D13C2=HSTACK(TOCOL(HSTACK(A2:A7,A2:A7+TIME(0,30,0))),TOCOL(IF({1,2},B2:B7)))
Dynamic array formulas.
 
Upvote 1
Solution
Fluff said:
Thanks for that.
How about
Fluff.xlsm
ABCD
1TimeStampsPriceTimeStampsPrice
201/06/2022 05:00192.4401/06/2022 05:00192.44
301/06/2022 06:00184.801/06/2022 05:30192.44
401/06/2022 07:00172.8701/06/2022 06:00184.8
501/06/2022 08:00167.301/06/2022 06:30184.8
601/06/2022 09:00161.3601/06/2022 07:00172.87
701/06/2022 10:00155.8301/06/2022 07:30172.87
801/06/2022 08:00167.3
901/06/2022 08:30167.3
1001/06/2022 09:00161.36
1101/06/2022 09:30161.36
1201/06/2022 10:00155.83
1301/06/2022 10:30155.83
14
Main
Cell Formulas
RangeFormula
C2:D13C2=HSTACK(TOCOL(HSTACK(A2:A7,A2:A7+TIME(0,30,0))),TOCOL(IF({1,2},B2:B7)))
Dynamic array formulas.
Click to expand...
That works great, thanks a lot.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Count of active employees between dates.
Replies
3
Views
879
Fluff
Fluff
M
  • Solved
Formula Help.
Replies
3
Views
481
Fluff
Fluff
N
  • Solved
Formula For Entering Loan Repayment On The Same Day For Each Month
Replies
4
Views
667
Dave Patton
D
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
300
CaptainGravyBum
C
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
550
dshtesy
D

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