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

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

Rate this: (3/5 from 1 vote)

	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","") 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 Michaniker on 21 Jan 2010

Replies - Displaying 1 to 1 of 1

Order Replies By: Most recent | Chronological | Highest Rated

	Solved 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 Michaniker on 23 Jan 2010

Displaying page 1 of 1