Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formula problem
Trying to add two rows of times with the result in one cell, but having
trouble. When I try to add the two rows together, I get a $VALUE result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis but I need to display just one result for both rows - in the Row 2 cell of the HOURS WORKED column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that 'zero' hours are not displayed. Row 1 formula: =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2))) Row 2 formula: =IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) Timesheet format is as follows: ACTUAL TIMES WORKED HOURS IN OUT IN OUT WORKED Row 1 8:00am 10:00am 11:30am 12:30pm 3* Row 2 1:30pm 5:00pm 6:00pm 8:00pm 5.5** *Do not want any hours to display here **Need total hours for both rows (8.5) to display here Thank you for any help you can send my way! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formula problem
Why not no formula at all in row1, and
=IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)))+IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) in row2? -- Regards! Stefi Leo ezt *rta: Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a $VALUE result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis but I need to display just one result for both rows - in the Row 2 cell of the HOURS WORKED column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that 'zero' hours are not displayed. Row 1 formula: =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2))) Row 2 formula: =IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) Timesheet format is as follows: ACTUAL TIMES WORKED HOURS IN OUT IN OUT WORKED Row 1 8:00am 10:00am 11:30am 12:30pm 3* Row 2 1:30pm 5:00pm 6:00pm 8:00pm 5.5** *Do not want any hours to display here **Need total hours for both rows (8.5) to display here Thank you for any help you can send my way! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formula problem
Thanks for your reply Stefi, but for some reason that formula returns a
"#VALUE" error. I have also tried the SUM function and it gives me the same result. Any other suggestions? Thanks again! "Stefi" wrote: Why not no formula at all in row1, and =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)))+IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) in row2? -- Regards! Stefi Leo ezt *rta: Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a $VALUE result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis but I need to display just one result for both rows - in the Row 2 cell of the HOURS WORKED column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that 'zero' hours are not displayed. Row 1 formula: =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2))) Row 2 formula: =IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) Timesheet format is as follows: ACTUAL TIMES WORKED HOURS IN OUT IN OUT WORKED Row 1 8:00am 10:00am 11:30am 12:30pm 3* Row 2 1:30pm 5:00pm 6:00pm 8:00pm 5.5** *Do not want any hours to display here **Need total hours for both rows (8.5) to display here Thank you for any help you can send my way! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet formula problem
See the answer to the same question you asked in
microsoft.public.excel.misc. -- David Biddulph Leo wrote: Thanks for your reply Stefi, but for some reason that formula returns a "#VALUE" error. I have also tried the SUM function and it gives me the same result. Any other suggestions? Thanks again! "Stefi" wrote: Why not no formula at all in row1, and =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)))+IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) in row2? -- Regards! Stefi "Leo" ezt rta: Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a "$VALUE" result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis -- but I need to display just one result for both rows - in the "Row 2" cell of the "HOURS WORKED" column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that 'zero' hours are not displayed. Row 1 formula: =IF((ISTEXT(A3)),"",IF(ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2)=0,"",ROUND((((B3-A3)+(D3-C3))-INT((B3-A3)+(D3-C3)))*24,2))) Row 2 formula: =IF((ISTEXT(A4)),"",IF(ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2)=0,"",ROUND((((B4-A4)+(D4-C4))-INT((B4-A4)+(D4-C4)))*24,2))) Timesheet format is as follows: ACTUAL TIMES WORKED HOURS IN OUT IN OUT WORKED Row 1 8:00am 10:00am 11:30am 12:30pm 3* Row 2 1:30pm 5:00pm 6:00pm 8:00pm 5.5** *Do not want any hours to display here **Need total hours for both rows (8.5) to display here Thank you for any help you can send my way! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
timesheet problem | Excel Discussion (Misc queries) | |||
Somewhat complex timesheet problem. | Excel Worksheet Functions | |||
Timesheet maximum hours problem | Excel Worksheet Functions | |||
Timesheet problem | Excel Discussion (Misc queries) | |||
Timesheet Problem | Excel Discussion (Misc queries) |