Forum Discussion
Solved
Time formula only accepts certain values
So, this one is a conudrum! See JAN tab of attached: the OPS (J) col. has a total formula at the top (cell J3). This formula works, but only as long as any of the manually-entered times are less than...
I suspect the issue is that the manual entry of 417 is interpreted into some 10000+ hours and that is causing a problem. Try this formula:
=TEXT(SUM(--IF(J4:J36="",0,IF(--J4:J36<1,J4:J36,TEXT(J4:J36,"00\:00")))),"[hh]:mm;@")I checkif J4:J36 is <1 (i.e. less than 1 day) but if not you could easily make that larger as long as it is less than the manual entry value which I assume the smallest would be 100 for 1:00
Does this formula for J3 help?
=SUMPRODUCT(--ISFORMULA(J4:J36)*IF(J4:J36="",0,J4:J36))
Thanks JK. It does indeed, thank you!