Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#VALUE error add'g formulas
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. I have tried using () around both formulas and adding, and have also tried the SUM function but for some reason those both give me the same "#VALUE" error. 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.misc
|
|||
|
|||
#VALUE error add'g formulas
First, you can shorten your formula as:
=IF(OR(ISTEXT(A3),ROUND(MOD(B3-A3+D3-C3,1)*24,2)=0),"",ROUND(MOD(B3-A3+D3-C3,1)*24,2)) Second, you didn't tell us how you tried to sum the two columns. What formula did you use? If it were me, I would hide column E, and in F4 use: =e3+e4 That would get the display you are looking for. If for some reason, you don't want to hide E, then remove your formula from E1, and in E2, change: B3-A3+D3-C3 to: B3-A3+D3-C3+B4-A4+D4-C4 Regards, Fred "Leo" wrote in message ... 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. I have tried using () around both formulas and adding, and have also tried the SUM function but for some reason those both give me the same "#VALUE" error. 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.misc
|
|||
|
|||
#VALUE error add'g formulas
Remember that standard AM and PM format for Excel has a space between the
number and AM or PM. 8:00 AM is a time 8:00AM is a text string. The text string gives you the #VALUE! error. -- David Biddulph Leo wrote: 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. I have tried using () around both formulas and adding, and have also tried the SUM function but for some reason those both give me the same "#VALUE" error. 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 | |||
Same formulas, but one returns a #DIV/0! error | Excel Discussion (Misc queries) | |||
Conditional Formulas--Suppressing error values? | Excel Discussion (Misc queries) | |||
Existe un error en la nomenclatura de las formulas financieras: | Excel Worksheet Functions | |||
how to minimize rounding error with complicated formulas | Excel Discussion (Misc queries) | |||
Excel Error when copying formulas | Excel Worksheet Functions |