I have a time-card worksheet in which I need to round time values up or down
to the nearest 1/4 hour based on the actual time an employee punches in. If
the punch in/out time is less than or equal to 7 minutes past the 1/4 hour
then the time is rounded down to that 1/4 hour. If the punch time is
greater then 7 minutes past the hour, then the time is rounded up to the
next 1/4 hour.

Example...an employee punches in at 5:33 am and punches out at 3:09 pm.

If I simply add the two times together then you get 9:36 hours worked for
the day. However, when rounding to the nearest 1/4 hour the punch in time
changes to 5:30 am and the punch out time changes to 3:15 pm. The total
hours worked for the day is not 9:45.

How can I acomplish this?

Thanks

Why not round the results of the difference.

--
Regards,
Tom Ogilvy


"GreenMonster" wrote in message
...
I have a time-card worksheet in which I need to round time values up or
down
to the nearest 1/4 hour based on the actual time an employee punches in.
If
the punch in/out time is less than or equal to 7 minutes past the 1/4 hour
then the time is rounded down to that 1/4 hour. If the punch time is
greater then 7 minutes past the hour, then the time is rounded up to the
next 1/4 hour.

Example...an employee punches in at 5:33 am and punches out at 3:09 pm.

If I simply add the two times together then you get 9:36 hours worked for
the day. However, when rounding to the nearest 1/4 hour the punch in time
changes to 5:30 am and the punch out time changes to 3:15 pm. The total
hours worked for the day is not 9:45.

How can I acomplish this?

Thanks

GM,

this will round a "punch time" according to your specs.

=MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)

and avoids the use of rounddown and or mround
thus avoiding the necessity of analysis toolpak addin..

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


GreenMonster wrote :

I have a time-card worksheet in which I need to round time values up
or down to the nearest 1/4 hour based on the actual time an employee
punches in. If the punch in/out time is less than or equal to 7
minutes past the 1/4 hour then the time is rounded down to that 1/4
hour. If the punch time is greater then 7 minutes past the hour,
then the time is rounded up to the next 1/4 hour.

Example...an employee punches in at 5:33 am and punches out at 3:09
pm.

If I simply add the two times together then you get 9:36 hours worked
for the day. However, when rounding to the nearest 1/4 hour the
punch in time changes to 5:30 am and the punch out time changes to
3:15 pm. The total hours worked for the day is not 9:45.

How can I acomplish this?

Thanks