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
Stacey McDonald

# 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

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:

Thanks,
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.
Stacey McDonald
Shri:
Thanks for the reply.  I am getting the following when trying to check the syntax on the formula you provided.  Can you assist?
Stacey McDonald

Shri:
Can you assist with the code you provided?  The syntax is not validating.