| 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 Topic | Post Reply Login |
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 | ||
| Replies - Displaying 1 to 1 of 1 | Order Replies By: Most Recent | Chronological | Highest Rated |
 | Rate this: (3/5 from 1 vote) 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 |
| 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 Excel Business Solutions Directory |