Calculate whether a 24hr SLA was met using a Start and End Date/Time

[mrlister2000]

Hello everyone
First time poster so please be gentle!

on my spreadsheet i have the following fields :-

[TABLE="width: 461"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Date and Time Received[/TD]
[TD]Date and Time Sent[/TD]
[TD]Time Taken[/TD]
[TD]Number of Hours[/TD]
[TD]Within SLA?[/TD]
[/TR]
[TR]
[TD]04/03/2013 15:38[/TD]
[TD]20/03/2013 13:39[/TD]
[TD]15 days 22:00[/TD]
[TD]97[/TD]
[TD]YES[/TD]
[/TR]
</tbody>[/TABLE]

What's bugging me is that the number of hours is 97, yet the SLA is more than 24hrs so it should read NO. if it's 24hrs or less then it should read YES

I've used the following formulas :-

Time Taken : =INT(E4-D4) & " days " & TEXT(MOD(E4-D4,1),"[h]:mm")

Number of hours : =(NETWORKDAYS(D3,E3)-1)*("5:15 PM"-"9 AM")+MOD(E3,1)-MOD(D3,1)

Within SLA : =IF(G3<24, "YES","NO")

Please note that I am no expert at Excel so don't be fooled with the formulas above. They've been acquired or I've had assistance!

Thank you in advance for your help

 

[Maagaard]

I can't see what's wrong here, but try: SLA : =IF(Value(G3)<24, "YES","NO"), instead, that might help.

 

[mrlister2000]

I can't see what's wrong here, but try: SLA : =IF(Value(G3)<24, "YES","NO"), instead, that might help.

That didn't work unfortunately, but thank you for your message.

I've set the Number of Hours column format to [h] if that helps anyone? I had it previously set at [h]mm:ss and that showed the number of Hours as 97:00:10

I'm wondering if the SLA formula is reading the 10 seconds as less than 24 and making it YES. Does that make sense?

 

[barry houdini]

To get 97 in G3 that cell must be custom formatted as [h], so you are not comparing like with like because excel takes 24 to be 24 days and G3 is less than that because it's 97 hours

Change to this version=IF(G3<"24:00"+0, "YES","NO")

 

[mrlister2000]

To get 97 in G3 that cell must be custom formatted as [h], so you are not comparing like with like because excel takes 24 to be 24 days and G3 is less than that because it's 97 hours

Change to this version=IF(G3<"24:00"+0, "YES","NO")


Thank you Barry Houdini, that worked perfectly! (love the ID name!)

 

[mrlister2000]

just one more question that i forgot to put in my original post.

when calculating the Time Taken and subsequently the Number of Hours, I'm taking it that this is based on a 7-day week?

If so, can it be done in such a way that weekends aren't included please?

Thank you in advance once again