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

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.

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!

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.

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??

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.

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.

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.

That would be fine. Thank you so much for the help!

Ask Your Question

Weekly Poll