Case Field: what hour was a case opened - Time zone & daylight savings time sensitive

Question

So using this formula I can get the Hour in GMT that the case was opened

VALUE(MID (TEXT ( CreatedDate), 12, 2))

However, we have users who are across the country. When a user views the report they want to see the hour based on their timezone. What makes it more interesting is Daylight savings time needs to be kept in consideration.

For example a Central Time zone user during the summer months can take GMT - 5 and get the Central time. In the tiner it's GMT - 6.

What are the options to provide a field that displays the hour opened contextually based on the time zone & day light savings of the viewing user? Any way to do this without resorting to code?

Answer 1

You can't access the timezone of a running user in a formula so there is no pure formula solution.

What you can do is create an hours_offset field on user, populate it via trigger, and then use that in any formulas to add/subtract the appropriate number of hours from GMT. You will still need to add daylight savings logic either in your formula or in a scheduled job that updates the field on user.

hours offset code:

TimeZone tz = UserInfo.getTimeZone();
//use a static date if you will apply DST logic in formulas
Integer milliOffset = tz.getOffset(DateTime.newInstance(2012,11,23,12,0,0)));
//use right now if you are evaluating in a scheduled job.
Integer milliOffset = tz.getOffset(DateTime.now());