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

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

"Steve" wrote in message
...
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

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"})

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

On Nov 5, 2:58*pm, Steve wrote:
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.

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.
--
David Biddulph

"Fred Smith" wrote in message
...
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

"Steve" wrote in message
...
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

On Nov 5, 2:58*pm, Steve wrote:
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.

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,

"Fred Smith" wrote:

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

"Steve" wrote in message
...
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

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

Much appreciated,

Thanks again,

Steve

"Fred Smith" wrote:

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

"Steve" wrote in message
...
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