Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
karicheti pavani

# How to send mails only on Business days using a formula, Field? Any one Help me on the below one

Hi,
I create date Field when the recorder is submitted and after i created 3 formula fields for 3,5,6,days using that Date field Like Below,But still i receiving the mails on sunday i need only Business days mon-fri
For Example i submit the record on friday i receive 3 day mail on wed,5day fri,6day mon(next week) like
any one Help on this
CASE(
WEEKDAY(Submitted_Date_For_Approval__c + 3),
1, (Submitted_Date_For_Approval__c + 3) + 1 + FLOOR(3/5)*2,
2, (Submitted_Date_For_Approval__c + 3) + 1 + FLOOR(4/5)*2,
3, (Submitted_Date_For_Approval__c + 3) + 1 + FLOOR(5/5)*2,
4, (Submitted_Date_For_Approval__c + 3) + 1 + FLOOR(6/5)*2,
5, (Submitted_Date_For_Approval__c + 3) + 1 + FLOOR(7/5)*2,
6, (Submitted_Date_For_Approval__c + 3) + 1 + CEILING(4/5)*2,
7, (Submitted_Date_For_Approval__c + 3) - IF(1>0,1,0) + 1 + CEILING(4/5)*2,
null
)

CASE(
WEEKDAY(Submitted_Date_For_Approval__c + 5),
1, (Submitted_Date_For_Approval__c + 5) + 1 + FLOOR(3/5)*2,
2, (Submitted_Date_For_Approval__c + 5) + 1 + FLOOR(4/5)*2,
3, (Submitted_Date_For_Approval__c + 5) + 1 + FLOOR(5/5)*2,
4, (Submitted_Date_For_Approval__c + 5) + 1 + FLOOR(6/5)*2,
5, (Submitted_Date_For_Approval__c + 5) + 1 + FLOOR(7/5)*2,
6, (Submitted_Date_For_Approval__c + 5) + 1 + CEILING(4/5)*2,
7, (Submitted_Date_For_Approval__c + 5) - IF(1>0,1,0) + 1 + CEILING(4/5)*2,
null
)

CASE(
WEEKDAY(Submitted_Date_For_Approval__c + 6),
1, (Submitted_Date_For_Approval__c + 6) + 1 + FLOOR(3/5)*2,
2, (Submitted_Date_For_Approval__c + 6) + 1 + FLOOR(4/5)*2,
3, (Submitted_Date_For_Approval__c + 6) + 1 + FLOOR(5/5)*2,
4, (Submitted_Date_For_Approval__c + 6) + 1 + FLOOR(6/5)*2,
5, (Submitted_Date_For_Approval__c + 6) + 1 + FLOOR(7/5)*2,
6, (Submitted_Date_For_Approval__c + 6) + 1 + CEILING(4/5)*2,
7, (Submitted_Date_For_Approval__c + 6) - IF(1>0,1,0) + 1 + CEILING(4/5)*2,
null
)
karicheti pavani
Anyone Help on above one
Naresh Kaneriya 9
Hi karicheti pavani,

Please try with below formula that should correctly calculate reminder dates for business days-

CASE(
MOD(Submitted_Date_For_Approval__c - DATE(1985,6,24), 7),
0, (Submitted_Date_For_Approval__c + 3) + FLOOR((3 + 1) / 5) * 2,  // Sunday
1, (Submitted_Date_For_Approval__c + 3) + FLOOR((3 + 1) / 5) * 2,  // Monday
2, (Submitted_Date_For_Approval__c + 3) + FLOOR((3 + 1) / 5) * 2,  // Tuesday
3, (Submitted_Date_For_Approval__c + 3) + FLOOR((3 + 1) / 5) * 2,  // Wednesday
4, (Submitted_Date_For_Approval__c + 3) + FLOOR((3 + 1) / 5) * 2,  // Thursday
5, (Submitted_Date_For_Approval__c + 5) + FLOOR((5 + 1) / 5) * 2,  // Friday
6, (Submitted_Date_For_Approval__c + 5) + FLOOR((5 + 1) / 5) * 2,  // Saturday
null
)

You can adjust this formula for the 5 and 6-day reminders similarly. This formula uses the MOD function to calculate the day of the week, and based on that, it calculates the reminder dates considering only business days. It should properly skip weekends and give you the correct reminder dates for Monday to Friday. please let me know if you have any question for the same.
karicheti pavani
Hi Naresh Kaneriya,
Still i am receving saturday it is not working, if i submitted 9 it will get 14(mon) But I am Receiving next 3day date.can help me.