time

[securityman]

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

 

[Biff]

Hi!

Can someone explain how it works?
the formula in cell b1 is =(a2<a1)+a2-a1 (how many hours worked)

Excel evaluates dates as integer numbers. One day (24 hours) has a value of
1.

Time is the fractional part of a day (1). A new day starts at 12:00 AM (open
to interpretation) so you must account for that in dealing with time periods
that span past midnight.

The formula is using a boolean test to determine if the time span goes into
the next day.

(a2<a1) will evaluate to either TRUE or FALSE and those boolean values can
be used in math operations. TRUE = 1 and FALSE = 0. So,

02:29 is less than 17:00 so 1 is added to the value in A2 during the
calculation process. So,

A2<A1 = 1
1+A2 = 1.1034722222
1.1034722222 - A1
1.1034722222 - .70833333333

= (in h:mm format) 9:29

To round B1 to the nearest 15 minutes:

=ROUND(((A2<A1)+A2-A1)/(15/1440),0)*15/1440

OR:

=ROUND(((A2<A1)+A2-A1)/TIME(0,15,0),0)*TIME(0,15,0)

Then I would like for the macro to take those results and put them
into.....

Can't help ya with that one!

Biff

"securityman" <[email protected]>
wrote in message

 

[securityman]

Thanks, Biff

The round fuction worked perfectly.

I figured that was the way the other (a2<a1) worked, but wanted to make
sure.

I have some ideas about the other (getting the results from the cell in
my Hours sheet over to the correct cell in one of my Months sheet), so I
will keep working on that.


Thanks for the help,

Don