• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 HensonIT & 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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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