Excel Timesheet Rounding

[ChelseaNaomi]

Hi, I'm trying to develop a timesheet that will round to the nearest 30 minutes and I can't figure out what to use for a formula. Can anyone please help me? First time posting and any I'm looking for any help I can get. Please and thank you!!

1. * Rounding set to 30 minutes, an employee clocking in between 8:00AM and 8:29AM will have their time rounded down to 8:00 AM.
2. * Rounding set to 30 minutes, an employee clocking in between 8:30AM and 9:00 AM will have their time rounded up to 9:00AM.
All time should be rounded to thirty minute increments (i.e. 8:30, 9:00, 9:30 and 10:00AM). When totaling the number of hours for each day, they should be in increments of 30 minutes (30 minutes = .5 hours and 60 minutes = 1 hour).

This can be done per day, week, or two weeks as the time sheet is bi-weekly.

i.e.
If someone works 7:40am - 5:02pm, this is a 9 hour work day.
If someone works 7:30am - 5:15am, this is a 9.5 hour work day.
If someone works 7:45am - 5:15am, this is a 10 hour work day.
Etc....

Thank you!

 

[DRSteele]

Welcome to the forum.

I think this should help. For the sample you provided for someone who works 7:45am - 5:15am, this is a 9-hour work day for him, not 10 I think.

Note that this algorithm does not contemplate calculations for an employee who might work past midnight. For example, if he starts at 10:00 PM and finishes at 7:00 AM the next day, we will need to use a different method to calculate his hours.

Book1
	A	B	C	D	E	F
1	Time Format
2	Start	Stop	Start_Rounded	Stop_Rounded	Days	Hours
3	7:40:00 AM	5:02:00 PM	8:00:00 AM	5:00:00 PM	0.375	9.00
4	7:30:00 AM	5:15:00 PM	7:30:00 AM	5:00:00 PM	0.396	9.50
5	7:45:00 AM	5:15:00 PM	8:00:00 AM	5:00:00 PM	0.375	9.00
6	7:29:00 AM	5:31:00 PM	7:00:00 AM	6:00:00 PM	0.458	11.00
Sheet65

Cell Formulas
Range	Formula
C3	=IF(MINUTE(A3)>=30,CEILING(A3,"00:30"),FLOOR(A3,"00:30"))
D3	=IF(MINUTE(B3)>=30,CEILING(B3,"00:30"),FLOOR(B3,"00:30"))
E3	=D3-C3
F3	=E3*24

_{^{<strike>
</strike>}}

 

[ChelseaNaomi]

It should be 9.5 hour day not 10. My mistake! I will try this.

Thank you.