Comparing Times
 

I need to pay a shift differential for times worked as follows:
shift diff#1: Pay "X" if start time is from 1:00 p.m. through 8:59 p.m.
shift diff#2: Pay "X" if start time is from 9:00 p.m. through 2:59 a.m.

The time input sheet converts the start times to military time. Then I have
been trying to evaluate the start time for TRUE against the above statements.
If TRUE, then another cell calculates the total time X the shift diferential
and adds it to the total wages.

How do I write the function IF or AND for the shift diff#2.

This doesn't seem complete to me as it only addresses #2, but that is what
you asked

=IF(OR(start_time=2100,start_time<300,"X","")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Just Learning" <Just wrote in message
...
I need to pay a shift differential for times worked as follows:
shift diff#1: Pay "X" if start time is from 1:00 p.m. through 8:59 p.m.
shift diff#2: Pay "X" if start time is from 9:00 p.m. through 2:59 a.m.

The time input sheet converts the start times to military time. Then I
have
been trying to evaluate the start time for TRUE against the above
statements.
If TRUE, then another cell calculates the total time X the shift
diferential
and adds it to the total wages.

How do I write the function IF or AND for the shift diff#2.

=if(AND(A2=TIME(13,0,0),A2<TIME(21,0,0) ), 1.5, 1)

Since your deinitions are unclear will just include the first part
but if you have another time range for time and a half you
can use OR(AND(...,...), AND(..., ...)) in place of AND(...,....).

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Just Learning" <Just wrote in message
...
I need to pay a shift differential for times worked as follows:
shift diff#1: Pay "X" if start time is from 1:00 p.m. through 8:59 p.m.
shift diff#2: Pay "X" if start time is from 9:00 p.m. through 2:59 a.m.

The time input sheet converts the start times to military time. Then I have
been trying to evaluate the start time for TRUE against the above statements.
If TRUE, then another cell calculates the total time X the shift diferential
and adds it to the total wages.

How do I write the function IF or AND for the shift diff#2.

The reason I'm not addressing shift diff #1 is that it can easily be
evaluated for True or False in military time.

Start time = 1:00 a.m
Pay differential if start time is between 9:00 p.m. and 2:59 a.m.

The formula below still doesn't give me the right answer

e.g., 1:00 a.m. is later than 9:00 p.m., but evaluates as false if the
statement says: 1:00=21:00.


"Bob Phillips" wrote:

This doesn't seem complete to me as it only addresses #2, but that is what
you asked

=IF(OR(start_time=2100,start_time<300,"X","")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Just Learning" <Just wrote in message
...
I need to pay a shift differential for times worked as follows:
shift diff#1: Pay "X" if start time is from 1:00 p.m. through 8:59 p.m.
shift diff#2: Pay "X" if start time is from 9:00 p.m. through 2:59 a.m.

The time input sheet converts the start times to military time. Then I
have
been trying to evaluate the start time for TRUE against the above
statements.
If TRUE, then another cell calculates the total time X the shift
diferential
and adds it to the total wages.

How do I write the function IF or AND for the shift diff#2.

But there is an OR <300 as well, and that evalautes to True, at least it
does with me.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Just Learning" wrote in message
...
The reason I'm not addressing shift diff #1 is that it can easily be
evaluated for True or False in military time.

Start time = 1:00 a.m
Pay differential if start time is between 9:00 p.m. and 2:59 a.m.

The formula below still doesn't give me the right answer

e.g., 1:00 a.m. is later than 9:00 p.m., but evaluates as false if the
statement says: 1:00=21:00.


"Bob Phillips" wrote:

This doesn't seem complete to me as it only addresses #2, but that is
what
you asked

=IF(OR(start_time=2100,start_time<300,"X","")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Just Learning" <Just wrote in
message
...
I need to pay a shift differential for times worked as follows:
shift diff#1: Pay "X" if start time is from 1:00 p.m. through 8:59
p.m.
shift diff#2: Pay "X" if start time is from 9:00 p.m. through 2:59
a.m.

The time input sheet converts the start times to military time. Then I
have
been trying to evaluate the start time for TRUE against the above
statements.
If TRUE, then another cell calculates the total time X the shift
diferential
and adds it to the total wages.

How do I write the function IF or AND for the shift diff#2.