How to Calculate if a Range of Times Falls Between two Date/TimeS
Hi
Try this:
=IF(H2-INT(H2)<=TIMEVALUE("18:00"),IF(OR(I2-INT(I2)=TIMEVALUE
("18:30"),INT(I2)INT(H2)),"Worked During Supper",""),"")
Regards,
Per
On 22 Jan., 01:36, Michaniker
wrote:
I have a problem where I am tracking maintenance workers who forget and do
not clock out for supper. *Supper time is between 18:00 and 18:30 hours
daily. *I couldn’t figure out how formulate if a time range (18:00 - 18:30) *
fell between two Date/Time stamps so I compromised figuring if I could
illustrate that his time card showed he was still clocked in during the
middle of the supper mealtime, I would be able to confront him and remind him
of the need to clock out.
Row H is the Start Date/Time
Row I is the Stop Date/Time * *
Row J contains: *
=IF(AND(MOD(H1,1)<TIMEVALUE("18:15"),MOD(I1,1)TIM EVALUE("18:15")),"Worked
During Supper","")
This works most of the time. *But when row H is 12/14/2009 *16:00 and row I
is 12/15/2009 *01:05, I get a blank. *I should see “Worked During Supper”. *
a. *What am I doing wrong? *
b. *How could I expand the formula to include checking for the full time
range (18:00 - 18:30)?
Thanks,
Michaniker
|