How to Calculate if a Range of Times Falls Between two Date/Time S

M

Michaniker

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)>TIMEVALUE("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
 
P

Per Jessen

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check if time falls between two times? 5
Calculate hours worked outside regular shift using date/time and iff condition 3
Finding if a certain time falls between two other times: 1
How to calculate difference between two date & time along with working hours and days 2
calculate the time between to dates and times 2
how do I write an IF/other statement to capture certain time data? 1
Show value from one cell in another cell if today falls between a date range 5
Calculating Time 3

Top