please help with timekeeping in excel

[pms333]

I've downloaded a timekeeping worksheet and I need to set the time in
quarters and to subtract lunch in/out times.. we keep time in quarter hours
and I need a formula that will keep time in quarter hours/15 minute
intervals...

 

[Bob Phillips]

Take a look at http://www.xldynamic.com/source/xld.Rounding.html#time

 

[Jason Morin]

=ROUND(A1/TIME(,15,),0)*TIME(,15,)

HTH
Jason
Atlanta, GA

 

[Sicarii]

My timekeeping sheet is setup with a grid with employee names down say column
A, Row 2 has time in 15 min intervals, I put a 1 in each cell where they work
and use B/L for break/lunch. On a seperate sheet I have a "pretty" written
schedule that transforms the grid data into the times. I believe that Frank
Kabal provided me with this formula and it rocks.

If you need help, let me know.

Total Hours (In/Out time):
{=TEXT(INDEX(M!$A$2:M!$BC$2,MIN(IF(M!$D$4:M!$BC$4=1,COLUMN(M!$D$4:M!$BC$4)))),"hh:mm")
& " - " &
TEXT(INDEX(M!$A$2:M!$BC$2,MAX(IF(M!$D$4:M!$BC$4=1,COLUMN(M!$D$4:M!$BC$4))))+TIME(0,15,0),"hh:mm")}

1st Break:
{=TEXT(INDEX(M!$A$2:M!$BC$2,MIN(IF(M!$D$4:M!$BC$4="B",COLUMN(M!$D$4:M!$BC$4)))),"hh:mm")
& " - " &
TEXT(INDEX(M!$A$2:M!$BC$2,MIN(IF(M!$D$4:M!$BC$4="B",COLUMN(M!$D$4:M!$BC$4))))+TIME(0,15,0),"hh:mm")}

Lunch:
{=TEXT(INDEX(M!$A$2:M!$BC$2,MIN(IF(M!$D$4:M!$BC$4="L",COLUMN(M!$D$4:M!$BC$4)))),"hh:mm")
& " - " &
TEXT(INDEX(M!$A$2:M!$BC$2,MAX(IF(M!$D$4:M!$BC$4="L",COLUMN(M!$D$4:M!$BC$4))))+TIME(0,15,0),"hh:mm")}

2nd Break:
{=TEXT(INDEX(M!$A$2:M!$BC$2,SMALL(IF(M!$D$4:M!$BC$4="B",COLUMN(M!$D$4:M!$BC$4)),2)),"hh:mm")
& " - " &
TEXT(INDEX(M!$A$2:M!$BC$2,SMALL(IF(M!$D$4:M!$BC$4="B",COLUMN(M!$D$4:M!$BC$4)),2))+TIME(0,15,0),"hh:mm")}