Problem with formula with TIMEVALUE()

Question

I want to have a field that can be used as the basis for 5-minute buckets. So, for example 9:45, 9:46 ... 9:49 should all result in 9:45.

Computations with time values are a bit awkward, at least for me, but this Time formula should give the proper time:

TIMEVALUE(
TEXT(HOUR(TIMEVALUE(Date_time__c)) + 1) + ':' +
IF(MINUTE(TIMEVALUE(Date_time__c)) < 10, '0', '') +
TEXT(5 * FLOOR(MINUTE(TIMEVALUE(Date_time__c)) / 5)) +
':00.000'
)

But this doesn't work, I will explain what happens later on.

A simpler attempt is this Text formula:

TEXT(HOUR(TIMEVALUE(Date_time__c)) + 1) + ':' +
IF(MINUTE(TIMEVALUE(Date_time__c)) < 10, '0', '') +
TEXT(5 * FLOOR(MINUTE(TIMEVALUE(Date_time__c)) / 5)) +
':00.000'

This produces, for example, 9:45:00.000. But using this formula in a formula that uses TIMEVALUE() doesn't work: when the Time formula is TIMEVALUE('9:45:00.000'), I get 9:45 as a result. When I put the Text formula, that I quoted above, in a separate field time_text__c and try to use TIMEVALUE(time_text__c), I get empty values in list views and an error in a report:

An unexpected error occurred. Please include this ErrorId if you contact support: 1528268210-25120 (1696119439)

Summarizing, when I copy the values that the text formula produces into TIMEVALUE(), it works. But when I try to use the actual formula, which produces that exact text, I get empty results or errors.

Answer 1

Thank you SaiPraveenKakkirala, for getting to the bottom of this.

The final Time formula is:

TIMEVALUE(
IF(HOUR(TIMEVALUE(Date_time__c)) < 9, '0', '') +
TEXT(HOUR(TIMEVALUE(Date_time__c)) + 1) + ':' +
IF(MINUTE(TIMEVALUE(Date_time__c)) < 10, '0', '') +
TEXT(5 * FLOOR(MINUTE(TIMEVALUE(Date_time__c)) / 5)) +
':00.000'
)

So an extra 0 had to be inserted for all hours 0 - 9 (the formula looks funny because my company has timezone GMT + 1).

This smells like a Salesforce bug: if I have a Time formula TIMEVALUE('9:45:00.000'), I get a proper time 9:45. But if I have a Text formula that produces a Text '9:45:00.000' and I use that in a Time formula, then all of a sudden things stop working (list view, report).