0

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.

5
  • Is there any customization in your org. Because I tried using the below formule 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') and given some datatime. It is working as expected. Can you confirm what Datetime value you are using Nov 7, 2022 at 10:47
  • @SaiPraveenKakkirala It's an ordinary DateTime field. I am not aware of any custom settings for this type. Nov 7, 2022 at 11:35
  • Did you try the above formula field with return type as Time and What value of date time value you are using Nov 7, 2022 at 11:51
  • @SaiPraveenKakkirala I have edited the question, to show the Time and Text formulas. The question already mentioned which values I am using. Nov 7, 2022 at 12:17
  • 1
    Hi Thanks for confirming on it. I have observed it is not working if the time is less than 10 AM and working when time is greater than 10 AM. There should be some issue in the formula. Nov 7, 2022 at 12:44

1 Answer 1

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .