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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
62
Fluff
Fluff
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
352
Trebor76
T
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
316
Peter_SSs
Peter_SSs
G
  • Solved
Splitting Dates with Unique ID
Replies
6
Views
412
Akuini
Akuini
M
  • Question
Average weighted price - checking broker data
Replies
5
Views
212
madeye89
M

Forum statistics

Threads
1,215,043
Messages
6,122,812
Members
449,095
Latest member
m_smith_solihull

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