Time Formula Help

[John]

I am trying to create a formula that will assign a time to
a shift. For example, if my time is 5am and the shift
schedule is:

Shift 1 3am - 11am
Shift 2 11am - 7pm
Shift 3 7pm - 3am

the formula should return "Shift 1".

If the time is 1am, the formula should return "Shift 3".

The caviat is that the shift schedules can change from
week to week. For example, the next week the shifts might
change to:

Shift 1 2am - 10am
Shift 2 10am - 6pm
Shift 3 6pm - 2am

I'm getting stuck on how to handle times that cross into
the next day i.e. say 6pm - 2am.

If anyone has any ideas about how the formula or UDF could
be set up I would appreciate it.

 

[Stan Scott]

John,

There are probably better ways to do this (and I'm sure you'll hear them),
but here's what I'd do.

Set up a table like this and give it a range name "shiftTable":

0 3
3 1
11 2
19 3


For any time after noon, just add 12. Once you've got the table, you can
enter "9pm" in cell A2, and use this formula:

=VLOOKUP(LEFT(A2,LEN(A2)-2)+IF(RIGHT(A2,2)="pm",12),shiftTable,2)

Stan Scott
New York City

 

[Frank Kabel]

Hi
try
="Shift " & INT(((A1+(A1<3/24))*24-3)/8) +1

 

[John]

Frank

The formula works great - thanks again for your help.

 

[John]

Stan,

Thanks, it works! Appreciate your help.

-----Original Message-----
John,

There are probably better ways to do this (and I'm sure you'll hear them),
but here's what I'd do.

Set up a table like this and give it a range name "shiftTable":

0 3
3 1
11 2
19 3


For any time after noon, just add 12. Once you've got the table, you can
enter "9pm" in cell A2, and use this formula:

=VLOOKUP(LEFT(A2,LEN(A2)-2)+IF(RIGHT(A2,2) ="pm",12),shiftTable,2)

Stan Scott
New York City




.