Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sheree KennerSheree Kenner 

Report filter on business days

Hello, I am trying to create a formula so that I can filter report results based on Business Days (since you can't filter on Business Days). 

I need to convert the following Excel formula into a Salesforce formula so that I can filter out tickets that were created outside of business hours.

=IF(AND(WEEKDAY(OpenedDate, 2) < 6, HOUR(H7) > 7, HOUR(H7) < 19), "Business Hours", "On-Call")

Basically, I want Monday - Friday only, from 7:00 AM to 5:00 PM MT. 

I'm not very good at formulas and would appreciate any help someone with more experience could offer.

Thanks so much,
Sheree
AbhishekAbhishek (Salesforce Developers) 
Hi Sheree,

CASE( MOD( Date__c - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")

It is basically picking a Sunday in the past and using that as a reference to figure out what day of the week your date field is. Very creative.

From here: 

https://success.salesforce.com/answers?id=90630000000h1oXAAQ


For further reference, you can check the below blogs too,

https://salesforcemann.wordpress.com/2016/12/04/the-ultimate-salesforce-formula-to-add-business-days-to-a-date/

https://success.salesforce.com/answers?id=90630000000gn4OAAQ


I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks.
Sheree KennerSheree Kenner
Good morning, Thank you very much for your reply. Quick question, my field is BMCServiceDesk__openDateTime__c, and am getting an error using DATE. I’d also like to exclude records that are not within our business hours in the same query. =IF(AND(WEEKDAY(BMCServiceDesk__openDateTime__c, 2) < 6, HOUR(H7) > 7, HOUR(H7) < 19). Apologies for not being more specific about the field type in my inquiry. Basically, I want Monday - Friday only, from 7:00 AM to 5:00 PM MT. This does seem very creative and much simpler than other formulas I’ve seen! Thank you. Sheree
AbhishekAbhishek (Salesforce Developers) 
Can you check the blogs which I have provided and have to make minor changes based on your requirement?