Calculate SLA Time

[robcarr]

Hi,

I have to work out when an email would be out of SLA.

I need to be able to display a date and time 12 WORKING hours before the current date and time.

my working hours are

Monday 08:00 21:00
Tuesday 08:00 21:00
Wednesday 08:00 21:00
Thursday 08:00 21:00
Friday 08:00 21:00
Saturday 08:00 20:00
Sunday 10:00 18:00

I would like to do this via a formula rather than code, as agents will be using this and coding has to have the security level changed on all users pcs and this will be too fussy.

thanks



thanks

Hope this is of use, Rob.

 

[SkipVought]

hi,

add a column to your table for the delta time to subtract for that day
[tt]
Dy St En Delta
Saturday 8:00 20:00 11:00
Sunday 10:00 18:00 14:00
Monday 8:00 21:00 14:00
Tuesday 8:00 21:00 11:00
Wednesday 8:00 21:00 11:00
Thursday 8:00 21:00 11:00
Friday 8:00 21:00 11:00
[/tt]
Name the ranges.

if the current date/time is in F1...
[tt]
F1: =F1-TIME(12,0,0)-INDEX(Delta,MOD(F1,7)+1,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Hi Skip,

sorry to pour cold water on your idea, but shouldn't you take into account times that may have 12 hours subtracted that calculate as being in the same day ... eg. a Monday at 20:13 minus 12 hours is Monday at 08:13 ( same day ).



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Yea, I thot about that after I fired it off.

BUT the OP can use the data to calculate off current time and determine whether to subtract the delta or not. Rob, that's your assignment.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

I thought that you may have thot about that Skip

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

And I thot that you might have thot that I thot....

O. L.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

hi,

thanks for the updates so far.

I have come accross a problem.

if i set the date to 23/08/10 08:02

the "=F1-TIME(12,0,0)-INDEX(Delta,MOD(F1,7)+1,1)" shows
22/08/10 07:02, 7am is not an opening time, so it seems like it is wrong - or maybe I am a being a plank and have got it wrong. the 12 working hours go back to saturday.

Rob.


Hope this is of use, Rob.

 

[SkipVought]

There are several problems with the formula as posted.

Have you tried to work them out?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}