Calculating the SLA end date and time

Hello,

I'm new to this forum and hope somebody can help me. I want to calculate the SLA end date from a given start date. The SLA time is 30 hours and is only measured during working days.

If a job comes in over the 'weekend' the clock does not start ticking until 07:30 am Monday morning. If a job comes over during working days in the evening after 16:30 pm, the clock does not start ticking until 07.30 am next morning.

So monday 12.30 + 30 hrs = tuesday 18:30
Friday 14:30 + 30 hrs = tuesday 11:30

I have the following formula for a 4 hour SLA time but it didn't worked out for me to convert it to a 30hr SLA. Can you please help me out?

=WORKDAY(A2-13.5/24,1)+IF(NETWORKDAYS(A2,A2), IF(MOD(A2,1)*24<=13.5,MAX(MOD(A2,1)*24+4,12.5), MIN(MOD(A2,1)*24-5,12.5)),12.5)/24

Yes, that's (almost) totally correct!

monday 17.30 + 30 hrs = wednesday 11:30 (because you don't start counting until tuesday at 07:30) - Counting from 07:30 on tuesday plus 30 hrs gives wednesday 13:30!

It may seem odd, but the company we work with only starts a ticket during working days from 07:30 till 16:30. From that moment they have 30 hrs to repair it with the note that weekends and holiday's are excluded.

So they work from monday 07:30 till friday 16:30 (including the nights, excluding holidays), but they will only start tickets during working days from 07:30 till 16:30. From then on the clock starts ticking for 30 hrs (weekends and holidays excluded from friday 16:30 till monday morning 07:30)

Thanks for your effort!

You can try ,

Start time is C3 in Date and time format
Duration D3 in number format.

Please Login or Register  to view this content.

Originally Posted by daddylonglegs

I would do this as a two step process with this formula in B2 to get the time that the clock starts ticking

=IF(OR(NETWORKDAYS(A2,A2)=0,MOD(A2,1)>F$3),WORKDAY(A2,1)+F$2,MAX(A2,INT(A2)+F$2))

where F2 is day start (07:30 in your case) and F3 is day end (16:30)

Then in C2 to calculate the SLA end time

=IF(MOD(B2,1)+WEEKDAY(B2,3)>4+F$3-F$4,3-F$3+F$2)+B2+F$4

where F4 is the number of hours as a time value, i.e. 30:00 in your case

Note that this will work for variable values in F2, F3 and F4 as long as F3 > F2 and F4 can't span over more than one weekend, so no longer than approx 105:00 depending on F2 and F3 values.

The above only excludes weekends, you can add a holiday range into the NETWORKDAYS and WORKDAY functions in the B2 formula and the holidays will be taken into account when determining the "clock ticking" time/date.....but holidays won't be taken into account in C2. If you need to do that then that will be tricky, when you have holidays I assume the clock stops ticking e.g. for a Wed holiday from Tuesday at 16:30 and then restarts at 07:30 on Thursday?

Hi daddylonglegs, you are amazing! Your formula almost works! For most values it calculates the right time. However, I have some values popping out which cannot be calculated. The faults all have in common that the start date is during working days past 16:30. For instance;

Tuesday 26 february 2013 22:30
Wednesday 27 february 2013 16:50

Can this be solved?

Great efforts so far!

@ kvsrinivasamurthy; thanks for your effort, tried the formula but unfortunately didn't work...

Originally Posted by leongti

Hello,

I'm new to this forum and hope somebody can help me. I want to calculate the SLA end date from a given start date. The SLA time is 30 hours and is only measured during working days.

If a job comes in over the 'weekend' the clock does not start ticking until 07:30 am Monday morning. If a job comes over during working days in the evening after 16:30 pm, the clock does not start ticking until 07.30 am next morning.

So monday 12.30 + 30 hrs = tuesday 18:30
Friday 14:30 + 30 hrs = tuesday 11:30

I have the following formula for a 4 hour SLA time but it didn't worked out for me to convert it to a 30hr SLA. Can you please help me out?

=WORKDAY(A2-13.5/24,1)+IF(NETWORKDAYS(A2,A2), IF(MOD(A2,1)*24<=13.5,MAX(MOD(A2,1)*24+4,12.5), MIN(MOD(A2,1)*24-5,12.5)),12.5)/24

Can someone please explain this formula? where are we defining start time and end time of business hours in this formula?