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.

 

[David Biddulph]

The OP may need to be told that "a proper Excel time" is entered as 05:30
with a colon.
Remember also that your formula would give a time of 26 hours as the
mid-point if the inputs were 23:00 and 05:00. It would be displayed as
02:00 if formatted as hh:mm, but as 26:00 if formatted as [h]:mm. If you
are trying to compare the time of the mid-point with the shift start and end
times to get the shift name (presumably a lookup formula?), then it may be
wise to put another MOD(...,1) around the mid-point formula.

 

[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