Extracting Data from Table and rounding times to nearest hour / half hour

[SimonVOwen]

Hello All,

I want to be able to separate out this data and allocate it to daily HH segments.
For example ideally I would be able to pull out the data in B3 (16:00-19:59)
To show 16:00 and 20:00. And then multiple these values by 2.
As this data sett would fall between the 33rd and 40th half hour periods of the day. (this is 8 half hours segments)
I know I can individually code this pretty easy but was trying to utilise Left/Right with round() to pull out the data but round on 19:59 does not round to 20.

I need to use the same formula on 13 different data sheets in a standardized format ideally that could quickly pull out the half hours timings present in each Period.

Time periods	Period 1	Period 2	Period 3	Period 4	Period 5
Winter Peak	Summer Peak	Winter Shoulder	Night	Other
Monday to Friday November to February	16:00 - 19:59		07:00 - 15:59
Monday to Friday June to August		07:00 - 19:59
Monday to Friday March			07:00 - 19:59
All Year				00:00 - 06:59	All Other Times

<tbody>
</tbody><colgroup><col span="2"><col span="5"></colgroup>

 

[lhartono]

Try this

[COLOR=#d3d3d3]'[/COLOR]
[COLOR=#0000cd]=TEXT((ROUND(LEFT(B3,5)*24,0)/24),"HH:MM") & " - " & TEXT((ROUND(RIGHT(B3,5)*24,0)/24),"HH:MM")
[/COLOR][COLOR=#d3d3d3]'[/COLOR][COLOR=#0000cd][/COLOR]