| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum
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(E68,E6-8,0) Can someone please help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
=IF(B6="stat",8,ROUNDUP((C6-B6)*24-D6,1))
"Kevin" wrote: 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(E68,E6-8,0) Can someone please help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
Well that was easy, thanks alot it works great now
"Teethless mama" wrote: =IF(B6="stat",8,ROUNDUP((C6-B6)*24-D6,1)) "Kevin" wrote: 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(E68,E6-8,0) Can someone please help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
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 "Kevin" wrote in message ... 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(E68,E6-8,0) Can someone please help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
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 "Kevin" wrote in message ... Well that was easy, thanks alot it works great now "Teethless mama" wrote: =IF(B6="stat",8,ROUNDUP((C6-B6)*24-D6,1)) "Kevin" wrote: 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(E68,E6-8,0) Can someone please help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
Thanks T
works even better now "T. Valko" wrote: 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 "Kevin" wrote in message ... 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(E68,E6-8,0) Can someone please help |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
autosum
You're welcome. Thanks for the feedback!
Biff "Kevin" wrote in message ... Thanks T works even better now "T. Valko" wrote: 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 "Kevin" wrote in message ... 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(E68,E6-8,0) Can someone please help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I set up a shortcut key for Autosum? | Excel Worksheet Functions | |||
| How do i protect a worksheet in Excel and still use AutoSum? | Excel Discussion (Misc queries) | |||
| Autosum returns an incorrect zero value on the selected cells | Excel Worksheet Functions | |||
| Autosum | Excel Discussion (Misc queries) | |||
| autosum problems | Excel Worksheet Functions | |||
Linear Mode
