Roundup / Rounddown function

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" <[email protected]> wrote in message
news:[email protected]...
> 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