Rounding up/down time entries

[gmeeks]

I'm stumped on how to write a formula to accomplish this. Hopefully someone can help.

My start time at work is 7:00 AM. We have a 10 minute leeway either side of 7:00. If you clock in at 6:53, it rounds to 7:00. Punch in at 7:05, it rounds to 7:00. Punch in more than 10 minutes either side of 7:00 (6:45 for example) and there is no rounding. How can I write a formula to apply this rounding if I'm within the 10 minute rule?

(INFO - the "rounded" time can be shown in another cell, it doesn't have to be adjusted in the original entry)
This message was edited by gmeeks on 2002-09-26 15:05

 

[Juan Pablo González]

This test:

=ABS(A1-7/24)<=10/1440

will return TRUE if you're within 10 minutes. The 7/24 means 7:00 A.M., and the 10/1440 means 10 minutes. You could adjust the 7 and the 10 to use different rules.

 

[gmeeks]

Thanks for the reply. That will give me a true/false, but I still don't know how to change the original value based on the true/false?

 

[IML]

I'm sure a better way exists, but you could use this until something better is offered up
=IF(MINUTE(A1)>=50,(HOUR(A1)+1)/24,IF(MINUTE(A1)<=10,HOUR(A1)/24,A1))

where your time is in A1.

good luck

 

[PaddyD]

Don't know about 'better', but a little shorter (assuming your entries are time values only & dont include a date component):

=IF(OR(A2<(410/1440),A2>(430/1440)),A2,7/24)

Book1
	A	B	C	D
1	Source	Round	Shorter
2	06:45	6:45	6:45
3	07:00	7:00	7:00
4	06:50	7:00	7:00
5	07:09	7:00	7:00
6	07:10	7:00	7:00
7	07:15	7:15	7:15
8
Sheet3

Paddy

EDIT: or, following Juan:


=if(ABS(A1-7/24)<=10/1440,7/24,a1)

Paddy
This message was edited by PaddyD on 2002-09-26 16:45

 

[gmeeks]

Wonderful! Thank you everyone for the help.