Details about this thread
  • State Suggested Answer
  • Replies 5 replies
  • Answers 2 answers
  • Subscribers 322 subscribers
  • Views 187 views
  • Users 0 members are here
Related content from around SugarClub

How to remove weekend days from formula to count days between two date?

caitlyn Croasdale

We have a field on our custom approvals records module that counts the days between the start of the process to the approved date with the following formula. 

ifElse(greaterThan(strlen(toString($date_approved_c)),0),add(subtract(daysUntil($date_approved_c),daysUntil($process_start_date_c)),1),"")

how can we exclude weekends?

We have SLAs we want to put out and enforce with approval teams but knowing the data is skewed to include weekends, it doesn't seem fair to do so until we have a solve for removing weekend. Most approvals will happen within less than a day so also looking to see how to turn this into hours, then I'll need business hours. We do not have business centers in place yet either. 

  • 0

    Hi  this is not an easy question. Without specific customization we have to be pretty creative on this subject.

    I can imagine 2 possibilities:

    1. Roughly subtract weekend days given the difference between date start and date end

    Given the difference between date start and date end is 25 days, so we have between 3 and 4 weeks, so the business days is around 25 - 3 weekends, that means, business days = 19 days.

    I know, it is a roughly calculation

    2. Option 1 plus considering the weekday for both date start and date end

    Applying the sugarLogic function dayofweek on both date start and date end you will be able to identify the specific week day for both dates and to be able to precisely calculate the business days.

    I understand that you will need to create some custom fields to calculate some milestones for the whole calculation.

    I hope it sheds some light.

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0

    In the cases module you can find very powerful functions to calculate the resolution time of a case absolutely and respecting the business times of a business center for SLAs. You find these in the issue template include\SugarObjects\templates\issue\Issue.php.

    Check function calculateResolutionHours().

    Perhaps you can use or copy something from there in a logic_hook

  • 0

    Hi  ,

    André and Harald raise valid approaches to solving your business need. I had considered how many nested ifElse statements and dependent calculations  it would require to accurately calculate the number of business days and it quickly spiraled!

    As a simpler approach, we decided yesterday to add some new SugarLogic functions to our Upsert Calculated Fields plugin:

    • uafCountBusinessDays - counts business days between two dates
    • uafCountBusinessHours - counts business hours between two dates
    • uafCountBusinessMinutes - counts business minutes between two dates
    • uafBusinessDaysSince - counts business days lapsed since a date
    • uafBusinessDaysUntil - counts business days to go until a date

    These formulas all calculate business days based on a standard Monday - Friday workweek. The nice thing about the formulas is that there are optional parameters to also specify a time zone and specific operating hours of the week or by each day. Here is sample of what the formulas look like:

    The formulas support integer or decimal output. Here is what the various formulas look like on a task record with a start and due date:

    If you would like to learn more about Upsert Calculated Fields and how these formulas can help measure your SLAs, please contact us via our website!

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

  • 0 in reply to Chris Raffle

    Great response Chris!  I'm impressed that Upsert has so quickly added the function. 

    Bud Hartley | Cape Foulwind, NZ (and Oregon, USA)

  • 0

    hi  

    The formula you're after is here:

    floor(add(0.5,
add(
multiply(floor(divide(add(1,subtract(daysUntil(addDays($date_approved_c,365000)),daysUntil(addDays($process_start_date_c,365000)))),7)),5)
,
add(
min(
max(0,subtract(6,ifElse(equal(dayofweek($process_start_date_c),0),7,dayofweek($process_start_date_c))))
,
subtract(
add(1,subtract(daysUntil(addDays($date_approved_c,365000)),daysUntil(addDays($process_start_date_c,365000))))
,
multiply(floor(divide(add(1,subtract(daysUntil(addDays($date_approved_c,365000)),daysUntil(addDays($process_start_date_c,365000)))),7)),7)
)
)
,
max(
0,
subtract(
subtract(
add(1,subtract(daysUntil(addDays($date_approved_c,365000)),daysUntil(addDays($process_start_date_c,365000))))
,
multiply(floor(divide(add(1,subtract(daysUntil(addDays($date_approved_c,365000)),daysUntil(addDays($process_start_date_c,365000)))),7)),7)
)
,
subtract(8,ifElse(equal(dayofweek($process_start_date_c),0),7,dayofweek($process_start_date_c)))
)
)
)
)
))

    *Note; This will count all days, including both start and end date.

    Hope that helps,

    Vincent

    .

    CRM Business Consultant