Forum Discussion
Jeanmentions
Mar 05, 2019Copper Contributor
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! |
- TwifooSilver ContributorAssuming your addends are in A1:A7, the formula for total hours is:
=SUMPRODUCT(HOUR(A1:A7)+(MINUTE(A1:A7)/60))- JeanmentionsCopper Contributor
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.
- TwifooSilver ContributorUsing my formula, the sum, in general format, 36.57 hours.
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)
- JeanmentionsCopper Contributor
You're right I didn't realize that: =TEXT(D3-C3,"hh:mm"). how should it be written?
on cell Ctrl+1 and apply custom format as