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" <securityman.1qnaua_1118801113.2047@excelforum-nospam.com>
wrote in message
news:securityman.1qnaua_1118801113.2047@excelforum-nospam.com...
>
> 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
>

[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