Forum Discussion
rmmwilg
Jan 28, 2025Brass Contributor
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 4:17!
Explanation of column J:
Below J3, the cells are formatted 00\:00 (however, see further details below), while total cell (J3) is formatted [hh]:mm,@). The non-total cells (J4 thru J36) automatically, initially copy any time manually loaded in the associated B cell (format 00\:00) and subtract any values manually entered in associated cells of K and L columns (both format 00\:00). As mentioned, the J-column cells have to, also accept manual time entries (updates), so they are formatted as 00\:00; however, because of the automated copying of B cell values and calculating of K & L cells, the formula converts the automated results into format [hh]:mm,@. The formula in those cells is: =IF(B5<>"",TEXT((TEXT(B5,"00\:00")-TEXT(K5,"00\:00")-TEXT(L5,"00\:00")),"[hh]:mm;@"),""). As described, this can then be overwritten if there is an update (see J4 for example).
So, the challenge is getting the total cell (J3) (format [hh]:mm,@) to be able to add up the values from two, different, possible formats in the same cells. I wasn't even sure this was possible without using separate columns to tally the results and then bring the total of those columns back to J3, but it turns out that it is with the formula listed below. However, if the manual-entry in J4 is more than 4:16, J3 result changes to #VALUE!
Evaluation shows that the ISFORMULA part in first-half of J3's formula stops forcing a result of 0 (0 * anything always = 0!) like it should be doing if the manually-entered time in J4 is 4:17 or greater! Bizarre!
=SUMPRODUCT(--ISFORMULA(J4:J36)*TEXT(J4:J36,"[hh]:mm;@"))+SUM(--TEXT(IF(J4:J36="",0,J4:J36),"00\:00"))
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
- m_tarlerBronze Contributor
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
- rmmwilgBrass Contributor
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!
- m_tarlerBronze Contributor
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.
- JKPieterseSilver Contributor
Does this formula for J3 help?
=SUMPRODUCT(--ISFORMULA(J4:J36)*IF(J4:J36="",0,J4:J36))