+ Start a Discussion
Ben Merton 15Ben Merton 15 

Quite complex date/time and business hours question

Supposing I have a Work Order with Completion_Date_Time__c and I am trying to work out the Date_Time of material required based both on a Delivery_Time__c AND the Business Hours / Holidays.

So working backwards it would be just Completion_Date_Time__c MINUS the Delivery_Time__c MINUS the NON-Business Hours/Holidays.  But what if that calculation AGAIN lands on a holiday / crosses a holiday.  You would need to parse this out?  Or am I completely missing something?

Are there any methods that can perform this calculation?

Assuming you have all of your business hours and holidays in the system correctly (and are not assuming that salesofrce will know about the holidays), you can use the BusinessHours class [1] to do the math for you.  So with your fields above if Deliver_Time__c is in days you'll need to covert days to milliseconds and then subtract that to your Completion Date Time field.
BusinessHours bh = [
    select Id
    from BusinessHours
    where Name = '24x7' // Or whatever criteria you are using

Long daysInMilli = Delivery_Time__c *
    24 * // Hours per day
    60 * // Minutes per hour
    60 * // Seconds per minute
    1000; // Milliseconds per second

DateTime startDateTime = BusinessHours.add(
    bh.Id, // business hours to use
    Completion_Date_Time__c, // start date
    -daysInMilli // - days to add
NOTE: This code has not been tested and may contain typographical or logical errors

[1] https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_businesshours.htm
Ben Merton 15Ben Merton 15
This doesn't solve the problem at all. I am aware of the business hours class. The question is how will the LOGIC work given an END date and not a start date, with TWO time variables. If my completion is on a Wednesday and my delivery time is 7 days, that means I need delivery 7 days before excluding holidays. If there is on holiday, it means 8 days. But if, after performing this calculation, that 8th day also lands on another holiday, it would be 9 days. But until the calc is done, the class won't KNOW that the 8th day is also a holiday. It needs to iterate / parse out all the dates until there are no holidays and then return out a final number. How do I do this? Tel: +91 80 301 96496 Mob: +91 98 869 24292 (India) Mob: +44 7733 411 839 (UK) Email: ben.merton@unifize.com www.unifize.com
I'm sorry that I misunderstood your question, but I think the answer to your problem still lies in the use of the BusinessHours class.  I believe my confusion is in what your variables are (what type and what their use is) and what your end goal is.  I had the initial understanding that you had a end date that you knew (Completion_Date_Time__c) and a number of days to deliver (Delivery_Time__c) and you wanted to determine a start date for this excluding holidays.  Your most recent response makes it appear that you are trying to figure out the number of business days between the two time variables.  It may be useful if you can give me more information about what you are trying to accomplish.  Feel free to give me screenshots / diagrams (mspaint even) of what data you currently have and what data you are trying to get.  Example data (denoted with something like HAVE and NEED) would also be appreciated.