• Status: Solved
• Priority: Medium
• Security: Public
• Views: 268

I need to add my time and how to total

I created a excel time card and I need help with my time sheet add up my time and total.
0
jamesccnacmp
2 Solutions

Author Commented:
0

Commented:
You time values are formated as text! That way you can't sum them!
0

Author Commented:
So help me, that why I am asking for help sir!
time-5-4-11.xlsx
0

Commented:
Try formula below for E12

=IF(IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E11,TIMEVALUE(LEFT(E10,FIND("m",E10)-2)))<0,
IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E11,TIMEVALUE(LEFT(E10,FIND("m",E10)-2)))+"12:00",
IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E11,TIMEVALUE(LEFT(E10,FIND("m",E10)-2))))

Copy to other cells...
0

Commented:
Ignore previous..
Try formula below for E12

=IF(IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E10,TIMEVALUE(LEFT(E10,FIND("m",E10)-2)))<0,
IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E10,TIMEVALUE(LEFT(E10,FIND("m",E10)-2)))+"12:00",
IF(ISERROR(FIND("m",E11)), E11,TIMEVALUE(LEFT(E11,FIND("m",E11)-2)))-IF(ISERROR(FIND("m",E10)), E10,TIMEVALUE(LEFT(E10,FIND("m",E10)-2))))

Copy to other cells...
0

Commented:
Sample attached...
test.xlsx
0

Author Commented:
Do not understand. Do I place in each cell?
0

Commented:
Place at the sum cells, please refer to the attachment..
0

IT & Database AssistantCommented:
Rather than forcing a formula to recognise the time as a time value, why not enter the times as numbers in first place. You can use the formatting to show the times with AM/PM. Custome format "h:mm am/pm" (without the quotes).

The total time will then be simple as one less the other. You will have to tweak when working past midnight. Fairly simple logic - if finish less than start then the formula would be "24 hours minus start minus finish" rather than "finish minus start".

Custome format for then adding up the hours "[h]:mm" will go beyond 24 hours whereas other time formats will start again from zero if you go beyond 24

Cheers
Rob H
0

Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.