autosum

[Guest]

I am trying to make a monthly time sheet.


This is the layout

A B C D E
F G

Date Time Worked Lunch Total Reg O/T
Start End Enter Time Hours Hours Hours
24-Oct-06 6:30 AM 3:00 PM 0.5 8.0 8.0 0.0
25-Oct-06 6:30 AM 4:15 PM 9.8 8.0 1.8
26-Oct-06 6:30 AM 5:45 PM 0.5 10.8 8.0 2.8
27-Oct-06 6:30 AM 3:45 PM 0.5 8.8 8.0 0.8


Columns D, E, F, G and there totals are formatted to number with one decimal
point.

At the end of the month it automatically adds up the different columns.

If you enter 6:30 AM – 4:15 PM, in column E it rounds it from 9.75 to 9.8
hours which is what I want it to do. The problem is at the end when you tally
up all the hours in the different columns by using the auto sum button it is
not adding up what it sees in column E, it is adding up the exact times from
columns B and C.

eg. Even though it says 9.8 hours in column E, it is adding 9.75

The formula that I have used for Column E is

=IF(B6="stat",8,(C6-B6)*24)-D6

Column F

=MIN(E6,8)

Column G

=IF(E6>8,E6-8,0)

Can someone please help

 

[Guest]

=IF(B6="stat",8,ROUNDUP((C6-B6)*24-D6,1))

 

[Guest]

Well that was easy, thanks alot it works great now

 

[T. Valko]

Instead of formatting to show 1 decinal place you need to round WITHIN the
formula itself:

=IF(B6="stat",8,(C6-B6)*24)-D6
Returns 9.75 (with D6 being empty)

=IF(B6="stat",8,ROUND((C6-B6)*24,1))-D6
Returns 9.8 (with D6 being empty)

Biff

 

[T. Valko]

Are you sure you want the -D6 within the IF function? It changes the way
your result is calculated.

Are you sure you want Roundup? 9.71 gets rounded to 9.8

Biff

 

[Guest]

Thanks T

works even better now

Instead of formatting to show 1 decinal place you need to round WITHIN the
formula itself:

=IF(B6="stat",8,(C6-B6)*24)-D6
Returns 9.75 (with D6 being empty)

=IF(B6="stat",8,ROUND((C6-B6)*24,1))-D6
Returns 9.8 (with D6 being empty)

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks T

works even better now