Formula to return word based on time of day data is entered

[Dscalf1]

I need a formula to return the word (first, second or third) based on the time of day information is entered in another cell.

So for example if cell AT6 were to change at 13:45:00 i want the word first to show up in cell BA6

First: 6:01-14:00
Second: 14:01-22:00
Third: 22:01-6:00

 

[Special-K99]

You need to change your table slightly since 00:01 will not be greater than 22:01, so early morning hours will never be picked up as being in the Third group.
Do it this way.

In Sheet2!A1:C4

Third   00:00    06:00
First   06:01    14:00
Second  14:01    22:00
Third   22:01    23:59

in BA6
=INDEX(Sheet2!A$1:A$4,MATCH(AT6,Sheet2!B$1:B$4,1),1)

 

[tyija1995]

Another option:

Cell AT6 = 13:45:00

Formula for BA6:

IF(AT6="","",IF(OR(TIMEVALUE(TEXT(AT6,"HH:MM"))>11/12,TIMEVALUE(TEXT(AT6,"HH:MM"))<=1/4),"Third",IF(AND(TIMEVALUE(TEXT(AT6,"HH:MM"))>7/12,TIMEVALUE(TEXT(AT6,"HH:MM"))<=11/12),"Second","First")))