Data need to split 1 hour time intervel

[Thoufeeq]

I need to split data, i am having 24 hours data. the time interval also included in that columns. for example, input output Client Trans time csk sub 1:05 1am to 2am intervel rr red 2:07 2am to 3am intervel dd red 5:45 5am to 6am intervel rcb sub 5:47 5am to 6am intervel kkr red 7:25 7am to 8am intervel pjb sub 7:54 7am to 8am intervel srh red 16:05 16am to 17am intervel mi sub 16:18 16am to 17am intervel i need above output using of formula... <colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody> </tbody>

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

 

[shg]

	A​	B​	C​	D​	E​
1​	Client​	Trans​	time​	Interval​	​
2​	csk	sub	1:05​	1AM to 2AM	D2: =TEXT(FLOOR(C2, "1:00"), "ham/pm") & " to " & TEXT(CEILING(C2, "1:00"), "ham/pm")
3​	rr	red	2:07​	2AM to 3AM
4​	dd	red	5:45​	5AM to 6AM
5​	rcb	sub	5:47​	5AM to 6AM
6​	kkr	red	7:25​	7AM to 8AM
7​	pjb	sub	7:54​	7AM to 8AM
8​	srh	red	16:05​	4PM to 5PM
9​	mi	sub	16:18​	4PM to 5PM

 

[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