formula/version 2007
Hi there.
The formula below rounds the difference between B2 (out) and A2 (in) to the
nearest quarter. I guess you can adapt it to your demand:
=IF(OR(ISBLANK(B2),ISBLANK(A2)),0,TIME(TRUNC((B2-A2)*24,0),VLOOKUP(((B2-A2)*24-D2)*60,{0,0;7.5,15;15,15;22.5,30;30,30;37.5,45;45, 45;52.5,60;60,60},2,TRUE),0))
Regards,
Otávio
"JackyP" wrote:
help with time sheet formula
time in/time out/time in/time out/time in/time out
can use as much as 3 ins and outs per day
8:30 am to 12:30 pm and 1:30 pm to 4:30 pm, this should equal 7 hours worked;
what if a person enters
8:25 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearest quarter hour
this still should be 7 hours;
or 8:22 am to 12:30 pm and 1:30 pm to 4:30 pm, round to nearst quarter hour
this should be 7.25, not (15min.)
basicly it needs to round both ways to nearest quarter hour no matter what
time they enter.
|