Formula required for Assignment status

[Santhi28595]

Dear team,

I am looking out for a formula to get the status of cases received on a daily basis.
I m looking out for a count of cases assigned to each users that is arrived Before 2 and after 2.

I have updated​ the summary in the attached Excel.

Kindly review and assist me.

Appreciate all your assistance.

[avk]

"c2" =COUNTIF(Data!F:F,Sheet3!B5)
"c11" =COUNTIFS(Data!D:D,Sheet3!B11,Data!F:F,"Before 2")
In other cell make change as per criteria.

[BadlySpelledBuoy]

You can use COUNTIFS() to do this. For example, the below is in C11:
=COUNTIFS(Data!D:D,B11,Data!E:E,"Processor",Data!F:F,"Before 2")

See attached where I've put in formulas for all your numbers on Sheet3.

You don't really need the middle argument that is looking for "Processor" in some tables and "Checker" in the others, but I've included it in case you have a processor and a checker with the same name.


BSB

[Santhi28595]

Hi Thanks.

Does this formula counts #N/A as well ???

[Santhi28595]

Thanks Badlyspelledbuoy.

Appreciate the quick fix. In the vlookup function as #n/a the formula excludes it.

Am I correct?

[BadlySpelledBuoy]

Correct, the N/A values in your data are being excluded by those formulas.

BSB

[Santhi28595]

Thanks a lot for the quick fix !!!

[BadlySpelledBuoy]

Happy to help.

BSB

[Santhi28595]

Hi one more question, the time in the data sheet.

Just for my learning I am asking, how do you segregate time and day from that. We have both date and time in a single cell.

How does this gets calculated?

[BadlySpelledBuoy]

I'm not sure I understand the question. Do you just want to know how to split a date/time cell into it's individual components (a date in one cell and a time in another)?

If so then =INT(A1) will provide just the date and =MOD(A1,1) will provide just the time from a date/time in cell A1.

BSB

[Santhi28595]

Thank you. I have one more doubt, the stats in the data tab "before 2" and "after 2" I am updating manually.

Can you please let me know if a formula can be put up so it automatically captures the status...

[BadlySpelledBuoy]

Quite possibly, but it all depends on what "Before 2" and "After 2" actually mean and how you calculate them manually.

For example, does it mean before/after 2pm? If so then why is row 2 before when the time is 6pm?

BSB

[Santhi28595]

Hi. Let me explain clearly

See the before 2 pm is actually means 14:00. If you look at my data when the previous day (21/6/2019)after 6 pm i.e 18:00 will also be considered as before 2 pm next day.

That's why I quoted them as before 2 pm which is a cut off to process the cases.

[BadlySpelledBuoy]

So anything between 18:00 and 14:00 the next day is "Before 2" and anything between 14:00 and 18:00 on the same day is "After 2"??

BSB

[BadlySpelledBuoy]

Maybe this in F2 and copied down:

Please Login or Register  to view this content.

BSB

[Santhi28595]

You are correct !!

[Santhi28595]

Maybe this in F2 and copied down:

Please Login or Register  to view this content.

BSB

Thank you BSB. Appreciate all your assistance.

I just have a doubt on how to find the overdue for a case.

For example, I have a case maturity date as 29-06-19, this means that this is not overdue. Any date which is less than today's date should be considered as overdue.

Can you please let me know how do I find that using a formula..

[BadlySpelledBuoy]

If the maturity date is in A1 then =IF(A1< TODAY(),"Overdue","") would work.

If that's what you mean then upload another attachment showing exactly what you mean. Your previous attachment did not have "Maturity Date" as far as I can remember.

BSB

[Santhi28595]

Response much appreciated !!!

Thanks BSB !!!

[BadlySpelledBuoy]

No problem. Happy to help

BSB