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