formula/version 2007

[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.

 

[T. Valko]

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

 

[Otávio Alves Ribeiro]

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