Categorizing the Time into Time Slots

Hi All,

I have one time field which has the Date and time in Am and PM format, i need another column where it says in which hour it came in.Column A has the date and time, i need the column B to display in the below format.

Find below the details.

Time Time Slot
4/1/13 12:18 PM 12 PM to 1 PM
4/1/13 12:21 PM 12 PM to 1 PM
4/1/13 12:22 PM 12 PM to 1 PM
4/1/13 2:42 PM 2 PM to 3 PM
4/1/13 2:49 PM 2 PM to 3 PM
4/1/13 3:03 PM 3 PM to 4 PM
4/1/13 4:54 PM 4 PM to 5 PM
4/1/13 7:58 PM 7 PM to 8 PM

Is there any formula that i can use to acheive this.

thanks
Zaveed

Originally Posted by Special-K

Maybe

=TEXT(A9,"hh AM/PM")&" to "&TEXT(A9+TIMEVALUE("01:00:00"),"hh AM/PM")

Watch out for 23hrs

Thank You Special K,

It works as expected.

Zaveed