changing times into shifts

[jcontrer]

Hi there!
What i have:
I have a telephone bill in an excel spreadsheet and im trying to perform a
cost analysis on my company's calls during certain shifts. the phone bill
comes with a column that breaks down the time of the call in this format
HH:MM and then AM or PM.
What i need:
In this cost analysis i dont need specific times what i need is shifts. I
need to set shifts from 8:00 AM - 4:30 PM to be shift 1 from 4:31 PM - 7:59
AM to be shift 2. How do i go about automatically taking the information in
the time column and have it run through those conditions and populate in
another column as a shift and not a specific time.
Please help, i would much appreciate it

 

[Joe Mac]

Try this formula -

=IF(AND((VALUE(CONCATENATE("",A6,"")))>=TIMEVALUE("8:00
AM"),VALUE((CONCATENATE("",A6,"")))<TIMEVALUE("4:31 PM")),"Shift1","Shift 2")

Test Data that I placed in A6 - A9 and formula in B6 - B9 with A6 - A9
formmatted as "h:mm AM/PM"
10:11 AM Shift1
2:33 PM Shift1
1:59 PM Shift1
8:28 PM Shift 2