What could be causing inconsistent formula result?
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?
Answers

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

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")

I welcome all suggestions for simplifying.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!