Trouble with IF THEN function

[beccashoe]

Hi - I have a large list of times (about 500,000 rows)

I need to assign a certain word (1st, 2nd, 3rd shift) to certain times frames within this long list. All times between 6AM-3PM, I need to automatically assign the word '1st Shift, to come up in the row next to it. As follows, 3PM-9PM : 2nd Shift , 9PM- 6AM 3rd shift.

THANK YOU!!

 

[mumps]

Assuming that your list of times is in column A, place this formula in column B and copy the formula down to the last used row: =IF(A2="6AM-3PM","1ST Shift",IF(A2="3PM-9PM","2nd Shift",IF(A2="9PM-6AM","3rd Shift","")))

 

[rilzniak]

I created a legend so that you can change the times if needed but here's what I came up with:

=IF(AND(A2>=$B$2,A2<=$B$3),$C$2,IF(AND(A2>=$B$4,A2<=$B$5),$C$4,$C$6))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Current Time[/TD]
[TD]Shift Times[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]5:58 AM[/TD]
[TD]6:00 AM[/TD]
[TD]1st Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2:59 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD]3:00 PM[/TD]
[TD]2nd Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8:59 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9:00 PM[/TD]
[TD]3rd Shift[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5:59 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, 5:58 AM is an example of the cell you're looking to rank. The formula is in cell A4 but you can cut and paste it, just make sure to change it to an absolute column reference. You can also drag the formula down over the rows.

Is that what you were looking to do?

 

[beccashoe]

YES! Thank you so much!