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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

M
  • Solved
Formula Help.
Replies
3
Views
114
Fluff
Fluff
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
360
Trebor76
T
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
340
Peter_SSs
Peter_SSs
G
  • Solved
Splitting Dates with Unique ID
Replies
6
Views
425
Akuini
Akuini
M
  • Question
Average weighted price - checking broker data
Replies
5
Views
225
madeye89
M

Forum statistics

Threads
1,215,563
Messages
6,125,560
Members
449,237
Latest member
Chase S

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