Whenever a report is operated on Yesterday time range, on Mondays & a day after holiday it gives blank result considering the business non working hours. How can I exclude non working days from the business hours so that can yield result of Saturday records on Monday without changing the date manually?
You don't need to use
MOD(DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
anymore, Salesforce has a Formula Function called WEEKDAY that returns the number 1 (Sunday) thru 7 (Saturday). However keep in mind that CreatedDate is stored in UTC TimeZone when converting CreatedDate using a DATEVALUE Function
Custom Field
Datatype = Formula
Result = Checkbox
Formula =
DATEVALUE( CreatedDate )
>=
( TODAY() -
CASE( TODAY() ,
2, 2,
1 )
)
PS. There is no native way to do this for Holidays via setup & configuration, for that you'd need to create custom Metadata or something and amend the formula accordingly.
