Data need to split 1 hour time intervel

[Thoufeeq]

[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 5"]

input				output
Client	Trans	time
csk	sub		1am to 2am intervel
rr	red		2am to 3am intervel
dd	red		5am to 6am intervel
rcb	sub		5am to 6am intervel
kkr	red		7am to 8am intervel
pjb	sub		7am to 8am intervel
srh	red		16am to 17am intervel
mi	sub		16am to 17am intervel

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
[TD="width: 320, colspan: 5"]I need to split data, i am having 24 hours data. the time interval also included in that columns. for example,[/TD]

[TD="class: xl65, align: right"]1:05[/TD]

[TD="class: xl65, align: right"]2:07[/TD]

[TD="class: xl65, align: right"]5:45[/TD]

[TD="class: xl65, align: right"]5:47[/TD]

[TD="class: xl65, align: right"]7:25[/TD]

[TD="class: xl65, align: right"]7:54[/TD]

[TD="class: xl65, align: right"]16:05[/TD]

[TD="class: xl65, align: right"]16:18[/TD]

[TD="colspan: 4"] i need above output using of formula...[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Client​

[/td][td="bgcolor:#F3F3F3"]

Trans​

[/td][td="bgcolor:#F3F3F3"]

time​

[/td][td="bgcolor:#F3F3F3"]

Interval​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]csk[/td][td]sub[/td][td]

1:05​

[/td][td="bgcolor:#CCFFCC"]1AM to 2AM[/td][td]D2: =TEXT(FLOOR(C2, "1:00"), "ham/pm") & " to " & TEXT(CEILING(C2, "1:00"), "ham/pm")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]rr[/td][td]red[/td][td]

2:07​

[/td][td="bgcolor:#CCFFCC"]2AM to 3AM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]dd[/td][td]red[/td][td]

5:45​

[/td][td="bgcolor:#CCFFCC"]5AM to 6AM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]rcb[/td][td]sub[/td][td]

5:47​

[/td][td="bgcolor:#CCFFCC"]5AM to 6AM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]kkr[/td][td]red[/td][td]

7:25​

[/td][td="bgcolor:#CCFFCC"]7AM to 8AM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]pjb[/td][td]sub[/td][td]

7:54​

[/td][td="bgcolor:#CCFFCC"]7AM to 8AM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]srh[/td][td]red[/td][td]

16:05​

[/td][td="bgcolor:#CCFFCC"]4PM to 5PM[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]mi[/td][td]sub[/td][td]

16:18​

[/td][td="bgcolor:#CCFFCC"]4PM to 5PM[/td][td][/td][/tr]
[/table]

 

[Learn Excel in Tamil]

Hi Try this function

=TEXT(MROUND(A1,1/24),"[$-409]h:mm AM/PM;@")&" to "&TEXT(ROUNDUP(A1*24, 0) / 24,"[$-409]h:mm AM/PM;@")&" interval"

Note i have used "A1" as cell address, you change it as per you excel sheet