Forum Discussion
Time formula only accepts certain values
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
Hi m_tarler
Yes, thank you! I've tried numerous values from '1' (for 1min) up to 7:59, and so far, it's not been an issue. So, it looks good. thank you again!
Drilling down into the original formula, I pared it down to a basic (0*TEXT(J4,"[hh]:mm;@")) (J4 being the manually entered cell which overwrote the formula in that cell) which only worked up to value 4:16, and that should not be! 0 times anything is always zero; that is a mathematical absolute, so that seems to be a major flaw in Excel's programming! Regardless, your formula covers it. Great job!
I'm glad it is working for you. The problem, iirc, is not if 0 time any VALUE is always 0, it was that it was because 0*ERROR which is an Error. And not to be nitpicky but even 0* any value is not technically always 0 since 0 * infinity is another exception for which I don't recall the rules from 30+ years ago. So although Excel if far from perfect, it is like any tool and that you need to know or learn its limitations. I've learned so much from this community and have appreciated the sharing of information here very much.
Yeh, that gets into the debate about whether infinity is actually a number or a concept, lol! I hear you on the ERROR though, and yes, that would return an error, properly so. Thanks again my friend.