Sumation of times

[drinu]

Dear All

I'm trying to add how much an employee worked during a week; however my sum formula is not acting as it should.

Example

Date Time In Time Out Total

05-May-14 09:31:00 19:38:00 10:07:00
06-May-14 09:31:00 20:11:00 10:40:00
07-May-14 08:36:00 12:45:00 04:09:00
08-May-14 08:45:00 19:54:00 11:09:00
09-May-14 10:52:00 19:09:00 08:17:00

If you drag cursor on the total column: total is 44:22:00 - which is correct
When I sum(D1:D5): total is 20:22:00 - which is wrong

I also tried sum(D1:D5)*24 but wrong sum is also given.

Any help?

Thanks

[Fotis1991]

Hi

Which formula do you use? Works ok for me!

[Pete_UK]

Using your original formula, you need to use a custom format on the cell, set to:

[hh]:mm:ss

The square brackets prevent the hours from wrapping at 24.

Hope this helps.

Pete

[drinu]

Thanks Pete & Fotis

My cells where formatted as TIME not as custom.

It worked now.

Regards
Ad

[Fotis1991]

You are welcome and thanks for the feedback.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.