Count if and turnaround time formula

[tevel3]

Hi all,

New here, need some assistance. I've searched everything and everywhere, can't seem to find a solution, so perhaps you can help.

So the premise is as follows, I need to calculate turnaround time, anything older than 2 days is outside the agreed turnaround time, anything less than 2 days in within the agreed turnaround time.

Here's the formula for anything outside the turnaround time: =COUNTIFS($D$7:$D$972,"<"&B1-2,$G$7:$G$972, "Inbox")... there is another one just for those within the turnaround time.

When using the spreadsheet I just copy and paste all the emails in the outlook mailbox.

D7:D972 - date and time. B1 is current date. G7:G972 - is the folder the email sits in; there is another formula that counts all the completed emails that were moved to another folder and that were received on the same day as B1.

I need a solution that when an email is received on a Friday, Saturday and Sunday, that this portion of the formula

$D$7:$D$972,"<"&B1-2​

, will be B1-3, and anything received between Monday to Thursday will be calculated with

$D$7:$D$972,"<"&B1-2​

.

A bit extensive explanation but I trust will save some back and forth.

 

[tevel3]

Oh, just to clarify, although might be obvious, there reason for this is so that I don't want the clock ticking on the turnaround time right away for emails on Friday and weekend as these emails will go outside the turnaround time before I had a change to get to them.

 

[tevel3]

Anyone?

Thanks