Time

[StacyRenee79]

I've been trying to create a formula to add my drivers hours. I enter them by
day, and need a total for the week.

Here is how they are keyed in:
Monday Tuesday WednesdayThursday Friday TOTAL
9.50 9.50 8.15 9.23 8.50 6.08

Now when adding these times, if two times added together equal or go over 60
then I have to add 40.

Example 9.50 + 9.50 = 1900 "but I need to add 40" so my total should be 19.40
Same with 9.23 + 9.40 = 18.63 "but I need to add 40" so my total should be 19.03
But with 8.15 + 8.15 = 16.30 then that's fine

I have a formula that works but it will not return my FALSE value. Here is the
formula I have:

=IF(SUM(A1:B1)>0.6,SUM(A1:B1)+.40,SUM(A1:B1))

I've probably got you all confused, I know I am.

Is there something wrong with that formula?

Can you help???

 

[Aladin Akyurek]

My last guess:

=SUMPRODUCT(INT(A2:F2)+100*(A2:F2-INT(A2:F2))/60)

 

[Ian Mac]

On 2002-02-24 17:30, StacyRenee79 wrote:
Yeah I can do that but anything over 60 min. I need 40 added to that, in order for the value to be accurate.

Ex. 9.50 + 9.50 = 19.00 but time wise you have to add 40 for the correct hours. You can't add 50min. and 50min. to get 100min. It's actually 1 hour 40 min.

Stacey,

What I believe you should do is use the : as a separater rather than the point your using currently.

This will tell Excel that the value is TIMEVALUE() and the sum of 9:50 + 9:50 will result in 19:40. Also bear in mind that Excel will assume that anything over 24hours is a new day so 23:00 + 2:00 will be 1:00, so you'll need to change to custom format [h]:mm as suggested by Aladin.

if you still want to use general numbers like 9.5 but want the answer as TIMEVALUE I'd go with Aladin's formula but slightly ammended:

=(SUMPRODUCT(INT(A2:F2)+100*(A2:F2-INT(A2:F2))/60))/24

and use the same custom format on the cell.

If you don't use the /24 at the end it will return the answer as a decimal value so 9.5 is 9:30, (also a point made by aladin).

Time can get very confusing at first but keep pluging away and it'll all make sense.

keep it up

Ian Mac

 

[StacyRenee79]

It worked THANK YOU!!!!!!