Use of Conditional Formatting

[Monkeyboy2266]

Hi...please be gentle, as its my first post!

I'm attempting to use conditional formatting to return a response of "Mornings" , "Afternoons" or "Nights" from a column which has a time reference.

The boundaries are:-

Mornings are between 06:00 to 13:59
Afternoons are between 14:00 to 21:59
Nights are between 22:00 to 05:59

I've tried a couple of iterations but I've not been successful in my attempts...

Thanks in advance.
Rich

 

[AlanY]

try

=IF(AND(A1 > =TIME(6,0,0),A1 < TIME(14,0,0)),"Mornings",IF(AND(A1 > =TIME(14,0,0),A1 < TIME(22,0,0)),"Afternoons","Nights"))

 

[jkpieterse]

I think you are referring to creating a formula in a cell, not necessarily a conditional format?
Anyway...
Create a table like this:
00:00 Night
06:00 Morning
14:00 Afternoon
22:00 Night
(lets assume this is in cells A1:B4)
Then write a time in cell D1 and in E1 place this formula:

=VLOOKUP(D1,$A$1:$B$4,2)

 

[Richard U]

The best way to attack this is not with conditional formatting, but with a macro in the change event

This will change the value of the cell to "Mornings", Afternoons", or "Nights"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim int_target As Integer
 If Target.Value = "" Then Exit Sub
 If Not IsDate(Target.Value) Then Exit Sub
 
 int_target = Hour(Target.Value)
     If int_target > 5 And int_target < 14 Then Target.Value = "Mornings"
     If int_target > 13 And int_target < 22 Then Target.Value = "Afternoons"
     If int_target < 6 Or int_target < 21 Then Target.Value = "Nights"
End Sub

If you need it to be a formula for another cell then, write it like this...

Option Explicit

Public Function MakeShift(ByVal myTime As Range)
Dim int_myTime As Integer

    MakeShift = ""
    If myTime.Value = "" Then Exit Function
    If Not IsDate(myTime.Value) Then Exit Function
 
    int_myTime = Hour(myTime.Value)
    If int_myTime > 5 And int_myTime < 14 Then MakeShift = "Mornings"
    If int_myTime > 13 And int_myTime < 22 Then MakeShift = "Mornings"
    If int_myTime < 6 Or int_myTime < 21 Then MakeShift = "Mornings"
     
End Function

 

[Monkeyboy2266]

Many thanks to all of you that contributed. I hadn't considered using a VLookup, and as I'm familiar with this method, took that route...thanks again to all.