Record Aging Formula

We have a formula field that calculates the number of hours since a record was created vs. now

((NOW() - CreatedDate)*24)

I would like to modify this to only include business hours, Monday through Friday 8-5 Eastern and to no include weekends.
Any suggestions or direction would be appreciated.

Thanks,
Stacey

February 8, 2023
·
Answer
·
Like
0
·
Follow
0

Vinay (Salesforce Developers)
Hi Stacey,

You can try below formula.

CASE( MOD(StartDate__c - DATE(1900, 1, 7), 7), 0, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c-1)/5)*2, 1, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c)/5)*2, 2, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+1)/5)*2, 3, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+2)/5)*2, 4, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+3)/5)*2, 5, (StartDate__c) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2, 6, (StartDate__c) - IF(NumberOfDays__c>0,1,0) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2, null)

Reference:
http://blog.proton7group.com/2020/02/formulas-for-adding-business-days.html

Please mark as Best Answer if above information was helpful.

Thanks,

February 8, 2023
·
Like
0
·
Dislike
1

Shri Raj
IF(MOD(NOW() - CreatedDate, 1) >= 0.5, (CEILING((NOW() - CreatedDate)*24) - IF(WEEKDAY(NOW()) >= 2 AND WEEKDAY(NOW()) <= 6, IF(HOUR(NOW()) >= 8 AND HOUR(NOW()) < 17, 0, MIN(HOUR(NOW()), 17) - 8), 0) - IF(WEEKDAY(CreatedDate) >= 2 AND WEEKDAY(CreatedDate) <= 6, IF(HOUR(CreatedDate) >= 8 AND HOUR(CreatedDate) < 17, 0, 17 - MAX(HOUR(CreatedDate), 8)), 0)), FLOOR((NOW() - CreatedDate)*24) - IF(WEEKDAY(NOW()) >= 2 AND WEEKDAY(NOW()) <= 6, IF(HOUR(NOW()) >= 8 AND HOUR(NOW()) < 17, 0, MIN(HOUR(NOW()), 17) - 8), 0) - IF(WEEKDAY(CreatedDate) >= 2 AND WEEKDAY(CreatedDate) <= 6, IF(HOUR(CreatedDate) >= 8 AND HOUR(CreatedDate) < 17, 0, 17 - MAX(HOUR(CreatedDate), 8)), 0)) )

This formula will give you the number of business hours between the current time and the created date, excluding weekends.