 • 511K Members
• 5,594 Online
• 608K Conversations

# formula to calculate total time worked in a week.

Hello, I'm trying to write a formula to calculate how many hours were worked during the a week.  I either get an "value" message or 00:00. Can someone help me.

 07:17 01:05 06:59 04:56 06:21 04:56 05:00 #VALUE!
7 Replies

# Re: formula to calculate total time worked in a week.

In general it works. the only result is to be formatted as elapsed time [hh]:mm

It looks like in your case time is entered as text, that's why you have zero. You may test by =ISTEXT(A1)

# Re: formula to calculate total time worked in a week.

Assuming your addends are in A1:A7, the formula for total hours is:
=SUMPRODUCT(HOUR(A1:A7)+(MINUTE(A1:A7)/60))

# Re: formula to calculate total time worked in a week.

formula almost works, it did total to 13:36. when done on a calculator, it amounts to 35:15.  it seems we're almost there.

# Re: formula to calculate total time worked in a week.

You're right I didn't realize that: =TEXT(D3-C3,"hh:mm"). how should it be written?

# Re: formula to calculate total time worked in a week.

on cell Ctrl+1 and apply custom format as # Re: formula to calculate total time worked in a week.

Using my formula, the sum, in general format, 36.57 hours.

# Re: formula to calculate total time worked in a week.

Using that format for the sum, the formula must be revised as:
=SUMPRODUCT(—A1:A7)
It returns 36:34.
Related Conversations
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies