Home > Forum Home > Automating Data Analysis Tasks > How to Calulate if a Range of Times Falls Between two Date/Time Stamps Share

How to Calulate if a Range of Times Falls Between two Date/Time Stamps

Excel Help for How To Calulate If A Range Of Times Falls Between Two Date/time Stamps in Automating Data Analysis Tasks


Forum TopicPost Reply Login

How To Calulate If A Range Of Times Falls Between Two Date/time Stamps

Rate this:
(3/5 from 1 vote)
ConfusedI 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","")
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”.  What am I doing wrong?  How could I expand the formula to include checking for the full time range (18:00 - 18:30)?  
 Michaniker
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Happy
Rate this:
(3/5 from 1 vote)
Huron helped me answer this in the [http://www.excelforum.com/]  His answer was:
Your formula isn't working if the end time is after midnight because the time value is less than 18:15.

Try the following formula Code:

=IF(AND(MOD(H1,1)TIMEVALUE("18:00"),TRUNC(I1)>TRUNC(H1))),"Worked During Supper","")

This worked great!
 Michaniker
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for How to Calulate if a Range of Times Falls Between two Date/Time Stamps in the