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:00
09:50 10:30 0:40
10:10 na
10:40 11:35 0:55
11:12 12:15 1:03
15:15 na
12:10 13:15 1:05


See More: Skipping empty cells while totaling time

Report •

#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:43

I 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
I was given this spreadsheet to finish, I did not creat it or the TEXT formula. It only had about 100 entries when I got it and I added the rest (2803). We need the total time but every formula I used did not add correctly or not al all giving me #value errors.

The formula: =IF(ISERROR(C3-B3),"",C3-B3) gives me an incorrect answer because it thinks it is numbers not time. 10:40 11:35 00:95. Somehow with the TEXT formula the answer is correct: 10:40 11:35 00:55. I am forever learning new tricks of the trade in Excel and obviously need to learn more. I do appreciate your help. Is there a way to upload my spreadsheet so you can see my problems??


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
1
2
3 9:00 10:00 =IF(ISERROR(C3-B3),"",C3-B3) returns 1:00
4 9:50 10:30 0:40
5 10:10 na
6 10:40 11:35 0:55
7 11:12 12:15 1:03
8 15:15 na
9 12:10 13:15 1:05
10 =SUM(D3:D9) returns 4:43

Click 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 •

Ask Question