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

--
Biff
Microsoft Excel MVP


"JackyP" wrote in message
...
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.

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.