• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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
Asked:
jamesccnacmp
2 Solutions
 
jamesccnacmpAuthor Commented:
0
 
jppintoCommented:
You time values are formated as text! That way you can't sum them!
0
 
jamesccnacmpAuthor Commented:
So help me, that why I am asking for help sir!
time-5-4-11.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wchhCommented:
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
 
wchhCommented:
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
 
wchhCommented:
Sample attached...
test.xlsx
0
 
jamesccnacmpAuthor Commented:
Do not understand. Do I place in each cell?
0
 
wchhCommented:
Place at the sum cells, please refer to the attachment..
0
 
Rob HensonFinance AnalystCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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