Calculating difference between times on 2 dates

[bollard]

Hello

I want to find out whether an e-mail was replied to within a 3-hour Service
Level Agreement. This involves subtracting one date and time from another.

However, if an e-mail arrives after 4 p.m, as we close at 6 p.m., only 2
hours of that SLA are used up. This gives the team a further hour from 8 a.m.
next morning, to reply within SLA.

In other words, with the working day being 8 a.m. to 6 p.m., what formula
can I use to calculate if a reply is within SLA,i gnoring the hours when we
are closed?

I have the incoming date and time in Column D; the outgoing date and time in
Column E in format: 21/05/2008 18:15 (i.e. UK date format.)


Thanks.

 

[Sandy Mann]

Try:

=E3-D3-((INT(E3)-INT(D3))*14/24)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[bollard]

Hi Sandy

Thanks for your reply, but I don't understand how this would work.

If, for example, an e-mail arrives at 9:00 a.m. and is answered within the 3
hours, this formula wouldn't apply, surely?

 

[Sandy Mann]

Did you try it?

D3: 10/6/08 9:00
E3: 10/6/08 12:00

Formula returns 3:00

D3: 0906/08 17:00
E3: 10/06/08 :12:

Formula returns 5:00

Isn't that what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Re-reading your original post and want a text return not a time try:

=IF(E3-D3-((INT(E3)-INT(D3))*14/24)<=0.125,"Within SLA","Alan Sugar
saysYou're Fired!")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk