How to set date and time to next business date and time

[Mangeshkhati]

Hi All,
Is there any formula which will populate the next business day and time if i get a ticket during non business hours?
The business day starts at 5:00:00 PM IST and ends at 5:30:00 AM. Saturday and Sunday are holiday.
Example:
If i get a ticket on 26th June 4:30:15 PM, in next column the open date should be populated as 26th June 5:00:00 PM.


Regards,
Mangesh

 

[Mangeshkhati]

Please explain in more detail. Exactly how is the deadline calculated. Give some examples please

Hi Sijpie, Thanks for your interest in this post. Below is the detail.

I need to populate a deadline based on business hours. The business hours are from 5 PM to 5:30 AM, Saturday and Sunday are holidays.
To arrive at a deadline i need to add 5 business days to the Open date of a ticket. 1 business day = 12 hours and 30 minutes (5 PM to 5:50 AM).
I am able to get the open date based on business hours. However i am not able to find the deadline based on business hours.

If start date of a ticket is 02nd Jul 2015 4:30 AM then deadline should be 09th Jul 15 at 3:30 AM.​

 

[sijpie]

=WORKDAY(Opendate,5)

 

[sijpie]

Ah, that wil give you the 00:00 time.
So then =WORKDAYS(OpenDate,5)+MOD(OpenDate,1)

 

[Mangeshkhati]

Ah, that wil give you the 00:00 time.
So then =WORKDAYS(OpenDate,5)+MOD(OpenDate,1)

Thank you Sijpie,
Its working fine for 5 business days, what should be the formula for 24 business hours?
I have two different SLAs for 2 different groups.

 

[sijpie]

Well is that 24 business hours 2 working days or one working day? replace the 5 in WORKDAYS(OpenDate,5) with however many working days it is (1 or 2)

 

[Mangeshkhati]

Hi sijpie
Actually, one business day = 12 and half hours, can I replace it with 2? Will it become 25 business hours then?

 

[sijpie]

2 will give 24 hrs. How important is the 'missing' hour? There will be cases where it might end up just on the wrong date (a day too early). But is that fatal for your planning tool?

 

[Mangeshkhati]

In this case the deadline will be one hour prior(if duirng the business hour) to the actual deadline.

 

[sijpie]

Over five days that isn't criminal, yes? You can always add 1/24 but I think that may cause problems in other scenarios. I'll leave it to you. ?