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

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History