Time

StacyRenee79

New Member
Joined
Feb 15, 2002
Messages
5
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???
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
On 2002-02-22 16:50, StacyRenee79 wrote:
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

<snip>

Supposing that A2:F2 houses the keyed in hours,

in G2 enter: =SUM(A2:F2)

And, this is important, custom format G2 as [hh]:mm.

To custom format a cell,

activate the cell;
activate Format|Cells;
choose Custom on the Number tab;
enter in the Type box:

[hh]:mm
 
Upvote 0
On 2002-02-24 00:02, Aladin Akyurek wrote:
On 2002-02-22 16:50, StacyRenee79 wrote:
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

<snip>

Supposing that A2:F2 houses the keyed in hours,

in G2 enter: =SUM(A2:F2)

And, this is important, custom format G2 as [hh]:mm.

To custom format a cell,

activate the cell;
activate Format|Cells;
choose Custom on the Number tab;
enter in the Type box:

[hh]:mm

Thanks but nope doesn't work!
 
Upvote 0
On 2002-02-24 05:19, StacyRenee79 wrote:
On 2002-02-24 00:02, Aladin Akyurek wrote:
On 2002-02-22 16:50, StacyRenee79 wrote:
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

<snip>

Supposing that A2:F2 houses the keyed in hours,

in G2 enter: =SUM(A2:F2)

And, this is important, custom format G2 as [hh]:mm.

To custom format a cell,

activate the cell;
activate Format|Cells;
choose Custom on the Number tab;
enter in the Type box:

[hh]:mm

Thanks but nope doesn't work!

What result do you get?
 
Upvote 0
On 2002-02-24 05:48, Aladin Akyurek wrote:
On 2002-02-24 05:19, StacyRenee79 wrote:
On 2002-02-24 00:02, Aladin Akyurek wrote:
On 2002-02-22 16:50, StacyRenee79 wrote:
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

<snip>

Supposing that A2:F2 houses the keyed in hours,

in G2 enter: =SUM(A2:F2)

And, this is important, custom format G2 as [hh]:mm.

To custom format a cell,

activate the cell;
activate Format|Cells;
choose Custom on the Number tab;
enter in the Type box:

[hh]:mm

Thanks but nope doesn't work!

What result do you get?

I get an off the wall result.
 
Upvote 0
[/quote]

I get an off the wall result.
[/quote]

No wonder. I disregarded the fact that you enter times not as true times.

In G2 enter:

=(SUM(A2:F2)/24)*24

Format G2 as General.

For the values you provided:

{9.5,9.5,8.15,9.23,8.5,6.08}

I get:

50.96

Is this what you're looking for?
 
Upvote 0
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.
 
Upvote 0
Hi
If you are trying to add up time, you really need to separate your hours and minutes with a colon instead of a dot. If you then format your cells as Aladin suggests [hh]:mm you can just add them up and get the correct answer.
You can use the edit, replace function to change your dots to colons.
Derek
 
Upvote 0
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.

I don't believe I understand the logic you're using:

9.50 is often used as another notation for

9:30

2 x 9:30 = 19:00 (or, in decimal notation, 19.00)

By the way, it would help getting a direct answer, e.g., to what the result of 9.50 + 9.50 should be.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top