Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate if a Range of Times Falls Between two Date/Time S
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 couldnt 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate within a time range with only start date and end date | Excel Worksheet Functions | |||
Need a formula to determine if date falls within a date range then | Excel Worksheet Functions | |||
Calculate how much time falls between set start and stop times | Excel Worksheet Functions | |||
Need true or false if a date falls between a date range | Excel Worksheet Functions |