I have a formula field. The field should display a certain value depending on the local time of the day. For example, if it is between 9:00 and 12:00 - morning, 12:00 and 3:00 - afternoon, and so on.

Is there a way in Salesforce to get the local time or time offset in the formula field. The user could be any where in the world.

  • Do you want just to display one of two words on layout? Or you are going to use it later within reports etc. If only display try this May 19 '16 at 17:36
  • The reports may run off of the value of the field. e.g. morning, afternoon, etc. May 19 '16 at 17:39
  • There is going to be reporting off of it so I can't use that solution. :( May 19 '16 at 17:43
  • Let's assume you have two users. In sf and Boston. It's 11 am in SF vs 1 pm in Boston. User a creates record. User b runs report. Should formula show afternoon or morning? May 19 '16 at 17:52
  • You can use NOW() in the formula field to get the current local datetime.
    – RajeshShah
    May 20 '16 at 2:03

I have already demonstrated how to write a formula based on time of day here:

Email Templates: Conditional Greeting Based on Time of Day

The trick is getting the offset for a specific user, which is not trivial, and not possible via formula. In fact, I think you will need a trigger and a batch to make it work.

The first step is to create an Offset number field on the User object. Then I would create the following Apex class:

public with sharing class UserOffset
    public static void set(List<User> users)
        for (User user : users) setOffset(user);
    static Integer set(User user)
        Timezone tz = Timezone.getTimeZone(user.TimezoneSidKey);
        user.Offset__c = tz.getOffset(Datetime.now())/(1000*60*60);

Then you need to call this in a User trigger on before insert and before update. You also need to write a batch that updates it for all User records. You need to run this batch once when you deploy it, and then every time DST changes. You can have some fun figuring out the cron expression for that.

Once you have your Offset__c field set up, you can do:

CASE(VALUE(MID(TEXT(NOW() - ($User.Offset__c/24)), 12, 2)),
    0, "Night", 1, "Night", 2, "Night", 3, "Night", 4, "Night", 5, "Night",
    6, "Morning", 7, "Morning", 8, "Morning", 9, "Morning", 10, "Morning", 11, "Morning",
    12, "Afternoon", 13, "Afternoon", 14, "Afternoon", 15, "Afternoon", 16, "Afternoon",
    17, "Evening", 18, "Evening", 19, "Evening", 20, "Evening",
    21, "Night", 22, "Night", 23, "Night",
  • Wow...that's a lot of work to get the local time. We do not want to have it as something that we will need to maintain all the time. :( I really appreciate your help. May 24 '16 at 21:32
  • @AdityaPatil Yeah, I agree it's unfortunately complicated, but I believe it is the simplest approach. It won't take too much maintenance, you just have to figure out how to schedule the batch and implement the trigger. You should be able to do it in 1-2 days even without much platform experience. If you already have experience forming CRON strings that would help, but then again you could probably get some help on Stack Overflow.
    – Adrian Larson
    May 24 '16 at 21:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.