# Skipping empty cells while totaling time

August 16, 2011 at 10:18:08
Specs: Windows 7
 I am trying to add the total time worked on a spreatsheet. The 1st column is start time, 2nd column is end time, 3rd colum is the the time worked. I need to have a total at the end of 3rd column but there are several without an end time (2nd column) so I have a blank cell. I have tried several formulas like =IF(ISBLANK) but have not succeeded. Any help would be great appreciated.09:00 10:00 1:0009:50 10:30 0:4010:10 na 10:40 11:35 0:5511:12 12:15 1:0315:15 na 12:10 13:15 1:05

See More: Skipping empty cells while totaling time

#1
August 16, 2011 at 10:55:06
 What's wrong with a simple SUM function?Assuming your 3rd column is in C1:C7, =SUM(C1:C7) will return 4:43.What's wrong with that?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
August 16, 2011 at 21:23:34
 I inherited this spreadsheet, the format on the3rd column is:=TEXT(C3,"00\:00")-TEXT(B3,"00\:00")+(C3>B3) and the normal =SUM is not working.The answer to =SUM(D3:D2803) comes up with 3:11. I may need to change the formula =TEXT(C3,"00\:00")-TEXT(B3,"00\:00")+(C3>B3) but I am stumped of how to get military time to subtract and have a result in number of hours and minutes. Appreciate your help!

Report •

#3
August 16, 2011 at 22:05:35
 I used this with your example data:=IF(ISERROR(C3-B3),"",C3-B3)Summing those results returned 4:43I have no clue what that TEXT formula is supposed to be doing, unless there is something you are not telling us about Column B & C, just like you didn't tell us about the formula in Column D in your original post.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
August 17, 2011 at 10:26:21

Report •

#5
August 17, 2011 at 11:26:21
 With the following set up, I have no problem getting the correct answers.Note: Format all cells as Custom...h:mm` A B C D 123 9:00 10:00 =IF(ISERROR(C3-B3),"",C3-B3) returns 1:004 9:50 10:30 0:405 10:10 na 6 10:40 11:35 0:557 11:12 12:15 1:038 15:15 na 9 12:10 13:15 1:0510 =SUM(D3:D9) returns 4:43Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.`

Report •

#6
August 17, 2011 at 19:04:54
 When I format the cells they all turn to 00:00. They were previously formatted 00\:00. I think it is time to use a calculator. Thanks for all your assistance.

Report •

#7
August 18, 2011 at 15:39:00
 If I send you an email address via Private Message can you send me a copy of the file, even if it's just the columns with the "times"?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#8
August 18, 2011 at 18:06:46
 That would be fine. Thank you so much for the help!

Report •