Ask Search:
Lindsay LarsonLindsay Larson 

created during business hours checkbox

Hi There,

I am trying to create formula that will checkmark "Created During Business Hours" as true if the case is created between 8am - 3pm CST. I am not really sure where to start. Is anyone able to help? Let me know if you need more info. Thank you!!
Best Answer chosen by Lindsay Larson
Narender SinghNarender Singh
A formula like this should do:
AND(
TIMEVALUE(CreatedDate) >= TIMEVALUE("08:00:00.000"),
TIMEVALUE(CreatedDate) <= TIMEVALUE("15:00:00.000")
)

 

All Answers

Steve MolisSteve Molis
Would 8:00:00 - 2:59:59 PM be okay?  

Then you could use a Formula like this
AND( 
HOUR(TIMEVALUE( CreatedDate - (6/24) )) >= 8, 
HOUR(TIMEVALUE( CreatedDate - (6/24))) <= 14 
)




 
Narender SinghNarender Singh
A formula like this should do:
AND(
TIMEVALUE(CreatedDate) >= TIMEVALUE("08:00:00.000"),
TIMEVALUE(CreatedDate) <= TIMEVALUE("15:00:00.000")
)

 
This was selected as the best answer
Steve MolisSteve Molis
Actually, use this one instead
AND(
TIMEVALUE( CreatedDate - (6/24) ) >= TIMEVALUE("08:00:00.000"),
TIMEVALUE( CreatedDate - (6/24)) <= TIMEVALUE("15:00:00.000")
)

 
Narender SinghNarender Singh
@Steve

The documentation says TIMEVALUE returns the value in local time. That's why I didn't account for time zone. Am I missing something?
Steve MolisSteve Molis
PS.  If you want your Formula to automatically adjust for Local Time and Daylight Savings you can use this technique 

KNOWLEDGE ARTICLE
How do I convert a Date/Time field for any timezone?
https://help.salesforce.com/articleView?id=000181642&type=1
Steve MolisSteve Molis
@Narender Singh

I've always thought that system audit fields like CreateDate and Last Modified Date were stored in UTC, so you need to adjust for Local Time (I might be wrong)
Steve MolisSteve Molis
KNOWLEDGE ARTICLE
Date or Time calculations in Formula fields, Workflow field updates and validation rules
Knowledge Article Number 000005091  
https://help.salesforce.com/articleView?id=000005091&type=1&language=en_US
Narender SinghNarender Singh
You are right. I was aware of the fact that audit fieds are stored in UTC but I thought the TIMEVALUE will auto convert into local time zone.
Thanks!
Steve MolisSteve Molis
@Narender Singh 

You might be right on that though.  I'm still just playing around with the new Time and Date Formula Functions, exploring what they can (and can't) do
Lindsay LarsonLindsay Larson
Thank you Steve and Narender! I will check both formulas to see which one will work better for us. Stay tuned!