Re: time

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@xxxxxxxxxxxxxxxxxxxxx>
wrote in message
news:securityman.1qnaua_1118801113.2047@xxxxxxxxxxxxxxxxxxxxxxxx
>
> I have set up a spread*** 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 *** works perfectly.
>
> I wanted to set up another *** (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 *** - 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?action=getinfo&userid=8829
> View this thread: http://www.excelforum.com/showthread.php?threadid=379217
>


.