I have set up a spreadsheet with time based on what our company truck
drivers work. We work under a DOT rule of 70 hours / 8 days. That
means that the driver can not work more than 70 hours in an 8 day
period.

The sheets I have (one for each month) goes back 7 days plus the
current day and calculated how many hours a day the driver works and
subtracts it from 70, leaving how many hours they can work the next
day. If the calculation gets to 0 or above, the cell changes color to
alert me that the driver's 70 hours are up and he/she has to have 34
hours off before he/she can be on duty again.

That part of the sheet works perfectly.

I wanted to set up another sheet (in the same workbook) to put their
time in from their time card to automatically calculate how many hours
they worked for the day.
I have it set up for Sunday Monday Tuesday ........ Saturday.

Sunday 17:00
02:29 =9:29
(hours worked that day)

I got that working ok, except when they come on in the evening (say
5:00 pm) and work until after midnite (say 2:29 am). I found a formula
to make this work but don't understand how it works. Can someone
explain how it works?

cell -- a1 17:00 (time in)
cell -- a2 02:29 (time out)

the formula in cell b1 is =(a2<a1)+a2-a1 (how many hours worked)


Also I need a macro (maybe?) to change the results in cell b1:
1. when it says 5:07 -- change to 5:00
2. when it says 5:12 -- change to 5:15
3. when it says 5:35 -- change to 5:30

In other words -- 1-7minutes on the hour
8-22 minutes to the 15 minute mark
23-38 minutes to the half hour mark etc.....


Then I would like for the macro to take those results and put them into
the cells back on the month we are working on (on another sheet - same
workbook) --- so it will calculate how many hours are left for the
driver to work.

I can insert these hours manually by looking at the timecards (because
the driver has already put how many hours he worked that day on it),
but sometimes they don't figure them correctly, but it's best for the
compter to do this as above.

Clear as mud???? I can send the workbook if need be. It 1 mb.

Thanks for any help.....I'll keep working on the macro also....

Don


--
securityman
------------------------------------------------------------------------
securityman's Profile: http://www.excelforum.com/member.php...fo&userid=8829
View this thread: http://www.excelforum.com/showthread...hreadid=379217