formula/version 2007

  • Thread starter Thread starter JackyP
  • Start date Start date
J

JackyP

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.
 
The basic formula would be:

=ROUND((end-start)*24/0.25,0)*0.25

If there are multiple start/end times:

=ROUND(SUM(end1-start1,end2-start2)*24/0.25,0)*0.25

Format as General or Number
 
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
 
Back
Top