Hi all,
Need some help with SLA generation as I've expanded all my resources to find the answers and this is why this is my first post.
I'll try to be as thorough as possible.
The SLA computation in my customer's account has a 3 days SLA to complete tickets.
Example: -
Supported hours - 7am to 7pm (Monday to Friday), 7am to 2pm (Saturday) and off on Sundays.
If a ticket has been assigned to the team on 14th May 2018, they will have 3 days to complete dependent on the time it was assigned as well.
That ticket assuming it's been assigned at 7am sharp would give us 36 support hours to complete it, meaning it should be completed by 16th May 2018 before 7pm.
The tick here is that, if the effective date of request is 6 days larger than the assigned date, the calculation will backtrack.
Example: -
Assigned Date: 14th May 2018 7am
Effective Date: 30th May 2018 12am
Since the effective date is more than 6 days away from assigned date, the computation will mean, the effective date (looking at hours and mins), will actually be on 29th May 2018 1900 hrs instead.
With 29th May 2018 being the effective date, which means, to fulfill the SLA, I would have to backtrack 3 days before the effective date as the completion date.
Using manual calculation, it would mean 25th May 2pm would be my completion date (29th Tuesday - 12hours shift, 28th Monday - 12hours shift, 27th Sunday - OFF, 26th Saturday - 7hours shift and lastly 25th Friday - 5 hours shift).
Since 25th May 2pm would be my completion date, then I would have another 36 hours beforehand to prepare myself for this ticket.
Using manual calculation, it would mean on the 22nd May 2018 2pm, I should have commenced my work to ensure the ticket is well within SLA.
I actually need a formula that helps to take out the Sundays, but include the 7 hours on Saturday, excluding public holidays and then return a result on when is my commencement date for my work to start.
In laymen terms, it would mean the minute I put in assigned and effective date with the hours, mins and secs, it should return a date that excludes all the non working hours/days and then give me an actual commencement date.
If anyone can help, I'd be deeply appreciated.
Need some help with SLA generation as I've expanded all my resources to find the answers and this is why this is my first post.
I'll try to be as thorough as possible.
The SLA computation in my customer's account has a 3 days SLA to complete tickets.
Example: -
Supported hours - 7am to 7pm (Monday to Friday), 7am to 2pm (Saturday) and off on Sundays.
If a ticket has been assigned to the team on 14th May 2018, they will have 3 days to complete dependent on the time it was assigned as well.
That ticket assuming it's been assigned at 7am sharp would give us 36 support hours to complete it, meaning it should be completed by 16th May 2018 before 7pm.
The tick here is that, if the effective date of request is 6 days larger than the assigned date, the calculation will backtrack.
Example: -
Assigned Date: 14th May 2018 7am
Effective Date: 30th May 2018 12am
Since the effective date is more than 6 days away from assigned date, the computation will mean, the effective date (looking at hours and mins), will actually be on 29th May 2018 1900 hrs instead.
With 29th May 2018 being the effective date, which means, to fulfill the SLA, I would have to backtrack 3 days before the effective date as the completion date.
Using manual calculation, it would mean 25th May 2pm would be my completion date (29th Tuesday - 12hours shift, 28th Monday - 12hours shift, 27th Sunday - OFF, 26th Saturday - 7hours shift and lastly 25th Friday - 5 hours shift).
Since 25th May 2pm would be my completion date, then I would have another 36 hours beforehand to prepare myself for this ticket.
Using manual calculation, it would mean on the 22nd May 2018 2pm, I should have commenced my work to ensure the ticket is well within SLA.
I actually need a formula that helps to take out the Sundays, but include the 7 hours on Saturday, excluding public holidays and then return a result on when is my commencement date for my work to start.
In laymen terms, it would mean the minute I put in assigned and effective date with the hours, mins and secs, it should return a date that excludes all the non working hours/days and then give me an actual commencement date.
If anyone can help, I'd be deeply appreciated.
