What could be causing inconsistent formula result?

Julie@WD

I'm calculating HH:mm using the following logic & getting inconsistent results for the minutes calculation.

The formula is the same for all three scenarios, but the Current Year Delivery is multiplying the minutes by 100 compared to the others. All the source data is consistent. What am I missing here?

DKazatsky2

Hi (I cannot seem to find the name with an @ sign in it).

The reason this is happening is because the decimal point being used in the FIND is not always in the same position. With "792.250" the decimal in in the 4th position but with "94.250" it is in the 3rd position - this changes the formula.

The first 2 numbers resolve to ".25 * 60" but the 3rd resolves to "25 * 60".

A more reliable formula would be: =MID([Formula Product], FIND(".", [Formula Product]), LEN([Formula Product]) - FIND(".", [Formula Product]) + 1)

Hope this helps,

Dave

Julie@WD

I answered my own question again. I was using the wrong formula. When I use the MOD function to calculate minutes/remainder it works.

Combined, here's the formula in A38:

=IFERROR(IF(INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60) < 10, "0" + INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60), INT(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37) / 60)) + ":" + IF(MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60) < 10, "0" + MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60), MOD(SUMIFS({Duration}, {CURWK}, 1, {Event Type}, A$37), 60)), "00:00")

Julie@WD

I welcome all suggestions for simplifying.