Midpoint of times in shifts

[Steve]

If I have the following table/definitiion

A B note: the B column times could go into
different columns
Days 0800-1600
Swing 1600-2400
Graveyard 2400-0800

If I have an employee working 2100-0530 (9 PM - 5:30 AM). I want a formula
to show the midpoint of this schedule (= 1:15 am).
Then I want another formula( result) based on the 1:15 AM ( being between
2400-0800) to produce "Graveyard".

I could enter the times any way, e.g 5:30 am = 05.50 if necessary.

Basically, whatever their schedule is, I need a result of Days, Swing or
Graveyard depending on where their midpoint falls in the time ranges.

Thanks,

Steve

 

[Fred Smith]

I would do it this way:

1. Enter a proper Excel time for start and end in adjacent cells (ie, B2 and
C2)
2. In, say D2, enter =B2+MOD((C2-B2),1)/2 to get the midpoint

Regards,
Fred

 

[T. Valko]

I think this does what you want:

Times entered as true Excel times

A1 = start time = 9:00 PM
B1 = end time = 5:30 AM

=LOOKUP(HOUR(A1+(MOD(B1-A1,1)/2)),{0;8;16},{"Grave";"Day";"Swing"})

 

[Spiky]

If I have the following table/definitiion

A                    B        note: the B column times could go into
different columns
Days          0800-1600            
Swing        1600-2400              
Graveyard   2400-0800

If I have an employee working 2100-0530 (9 PM - 5:30 AM). I want a formula
to show the midpoint of this schedule (= 1:15 am).
Then I want another formula( result) based on the 1:15 AM ( being between
2400-0800) to produce "Graveyard".

I could enter the times any way, e.g 5:30 am = 05.50 if necessary.

Basically, whatever their schedule is, I need a result of Days, Swing or
Graveyard depending on where their midpoint falls in the time ranges.

Thanks,

Steve

=MEDIAN(A1,B1)
Whatever format you use, use it consistently. A 12 hour clock is
easier in Excel. And you have to make sure you know what date it is
assuming, too. If you just type in 9:00 pm and 5:30 am, it will assume
they are the same day and return 1:15 pm, not am. Times are messy in
general.

 

[Spiky]

If I have the following table/definitiion

A                    B        note: the B column times could go into
different columns
Days          0800-1600            
Swing        1600-2400              
Graveyard   2400-0800

If I have an employee working 2100-0530 (9 PM - 5:30 AM). I want a formula
to show the midpoint of this schedule (= 1:15 am).
Then I want another formula( result) based on the 1:15 AM ( being between
2400-0800) to produce "Graveyard".

I could enter the times any way, e.g 5:30 am = 05.50 if necessary.

Basically, whatever their schedule is, I need a result of Days, Swing or
Graveyard depending on where their midpoint falls in the time ranges.

Thanks,

Steve

Oh, and on the shift names, you can probably just do an IF since it is
only 3 and the names aren't likely to change.
=IF(A1-INT(A1)<0.33333333,"Graveyard",IF(A1-
INT(A1)<0.666666666,"Day","Swing"))

..33 and .66 are the actual numbers Excel uses for 8am and 4pm.

 

[Steve]

Thanks everybody,

Looking forwared to working with the solutions, but right now, I got majorly
sidetracked, and appropriately, time is at a premuim right now. The responses
are much apprecated.

Thanks again,

 

[Steve]

Thanks all. I was finally able to get to it, and it worked exactly as I wanted.

Much appreciated,

Thanks again,

Steve