L
Leo
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!
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!