Help to creat a time sheet

[Ahmez]

Hello,

Firstly, thank you very much to the great minds behind this website and its users.

I would like to create an out of office time-sheet for staff members who works overtime.

Our policy is we pay in 15 minutes block.

For example, say John has received a work-related phone call lasting 5 minute on Monday, we pay him for 15 minutes. On Tuesday he received a call lasting for 16 minutes, we pay him for 30 minutes. On Wednesday he received 2 calls, the 1^st call lasted for 13 minutes and 2^nd lasted for 12 minutes, what formula should I use for excel to add all the minutes worked on the Wednesday together and then round it up to 15 minutes block.
The number of call received per day will vary. On Thursday, John may receive 5 calls or 1 call. Each call is recorded on a different row.

I know how to round the minutes up to 15 minutes block. I will really appreciate your help in creating a formula for excel to add the minutes of call receive per day and then round up.


Thank you very much

 

[DRSteele]

If I understand correctly, this might be what you're looking for. Columns F and G comprise a report on the raw data.

Excel 2012
	A	B	C	D	E	F	G
1	Day	Call	Pay (per call)
2	Mon	5	15			Pay (per call basis)	Pay (per day basis)
3	Tue	16	30		Mon	15	15
4	Wed	13	15		Tue	30	30
5	Wed	12	15		Wed	30	30
6	Thu	5	15		Thu	105	75
7	Thu	7	15		Fri	90	75
8	Thu	12	15		Sat	15	15
9	Thu	16	30		Sun	60	45
10	Thu	21	30
11	Fri	1	15
12	Fri	16	30
13	Fri	15	15
14	Fri	29	30
15	Sat	11	15
16	Sun	6	15
17	Sun	37	45
Sheet50

Cell Formulas
Range	Formula
F3	=SUMIFS($C$2:$C$17,$A$2:$A$17,E3)
F4	=SUMIFS($C$2:$C$17,$A$2:$A$17,E4)
F5	=SUMIFS($C$2:$C$17,$A$2:$A$17,E5)
F6	=SUMIFS($C$2:$C$17,$A$2:$A$17,E6)
F7	=SUMIFS($C$2:$C$17,$A$2:$A$17,E7)
F8	=SUMIFS($C$2:$C$17,$A$2:$A$17,E8)
F9	=SUMIFS($C$2:$C$17,$A$2:$A$17,E9)
G3	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E3)/15,0)*15
G4	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E4)/15,0)*15
G5	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E5)/15,0)*15
G6	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E6)/15,0)*15
G7	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E7)/15,0)*15
G8	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E8)/15,0)*15
G9	=ROUNDUP(SUMIFS($B$2:$B$17,$A$2:$A$17,E9)/15,0)*15
C2	=ROUNDUP(B2/15,0)*15
C3	=ROUNDUP(B3/15,0)*15
C4	=ROUNDUP(B4/15,0)*15
C5	=ROUNDUP(B5/15,0)*15
C6	=ROUNDUP(B6/15,0)*15
C7	=ROUNDUP(B7/15,0)*15
C8	=ROUNDUP(B8/15,0)*15
C9	=ROUNDUP(B9/15,0)*15
C10	=ROUNDUP(B10/15,0)*15
C11	=ROUNDUP(B11/15,0)*15
C12	=ROUNDUP(B12/15,0)*15
C13	=ROUNDUP(B13/15,0)*15
C14	=ROUNDUP(B14/15,0)*15
C15	=ROUNDUP(B15/15,0)*15
C16	=ROUNDUP(B16/15,0)*15
C17	=ROUNDUP(B17/15,0)*15

 

[Ahmez]

Sorry for this late reply. Yes, this is exactly what I am after. Thank you very much, you must be very excellent in excel.

Ahmez

 

[DRSteele]

You're welcome, I'm glad I sufficed. Excellent? Not even close! but thanks for saying so.