More Than 24 hours and Less Than 24 Hours

[maheshrk81]

Hi All,

I have below tables which shows Query received time and Query resolved time. Now i need a formula in Column C to show, if the query is resolved with 24 hours or after 24 hours. Can someone please help

[TABLE="width: 642"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Query Received[/TD]
[TD]Resolved[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]2017-10-17 10:16:15[/TD]
[TD]2017-10-18 11:39:59[/TD]
[TD]More Than 24 Hours[/TD]
[/TR]
[TR]
[TD]2017-10-17 10:16:15[/TD]
[TD]2017-10-17 00:16:15[/TD]
[TD]Less Than 24 Hours[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Mahi

 

[pkdeimos]

Hi,

In excel, as date and time are represented as numbers (date is the whole number and time is the decimal part), you can simply subtract "Query Received" column from "Resolved" column. If the result is greater than 1, it took "More than 24 hr" to resolve the issue or else "Less than 24 hr".

In row#2 of your sample data, resolved time is earlier than the query received time. If the time is in 12-hr format, you may have to mention AM or PM for accurate results. Hope this helps.

Regards.

 

[FDibbins]

Try this...
=IF(B2-A2>1,"More","Less")&" than 24 hours"

Note that in your 2nd example, you resolved it before you received it

 

[maheshrk81]

Hi All,

Thanks a lot for the immediate response. This solved my issue. If i need to check for 48 hours, then what i need to use? Is it >2 or anything else?

Thank you once again for the help.

Mahi

 

[pkdeimos]

That's right.

Cheers

 

[barry houdini]

Note that in your 2nd example, you resolved it before you received it

Perhaps....

=IF(B2< A2,"Miracle",IF(B2-A2>1,"More","Less")&" than 24 hours")

 

[FDibbins]

Perhaps....

=IF(B2< A2,"Miracle",IF(B2-A2>1,"More","Less")&" than 24 hours")

haha good 1. Or maybe even...
=IF(B2< A2,"REALLY really good service",IF(B2-A2>1,"More","Less")&" than 24 hours")