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.

[TABLE="width: 1033"]
<tbody>[TR]
[TD="colspan: 2"]Time periods[/TD]
[TD]Period 1[/TD]
[TD]Period 2[/TD]
[TD]Period 3[/TD]
[TD]Period 4[/TD]
[TD]Period 5[/TD]
[/TR]
[TR]
[TD]Winter Peak[/TD]
[TD]Summer Peak[/TD]
[TD]Winter Shoulder[/TD]
[TD]Night[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Monday to Friday
November to February[/TD]
[TD]16:00 - 19:59[/TD]
[TD] [/TD]
[TD]07:00 - 15:59[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Monday to Friday
June to August[/TD]
[TD] [/TD]
[TD]07:00 - 19:59[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Monday to Friday
March[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07:00 - 19:59[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]All Year[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00 - 06:59[/TD]
[TD]All Other Times[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="5"></colgroup>[/TABLE]

 

[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]