Hello,
I have a workbook which tracks job status.
The stats worksheet is set up to keep track of a number of metrics, including how many jobs are on-time, how many jobs are late.
I am trying to get the table to where, if the job is set up as a "supplier" in the "customer" column of the status log worksheet then it's late status is ignored when calculating the number of late jobs. The stats page keeps track of the current and previous weeks. For the previous weeks I have it working well. The problem is with the current week.
Right now we have a supplier job which was due last week. It is running late.
This supplier job should show up in the "Supplier Late" row of the stats table (which it does), but it should not be counted in the "Number late" row (which it is). The job became late last wednesday, on thursday and friday (when it was still due during the current week) the formula I had for the 'current week' column 'number late' row worked at keeping the supplier job out of the count.
Now that the due date is in the previous week my formula =COUNTIF(Table2[On-time],"No")-(COUNTIFS(Table2[On-time],"No",Table2[Due Week Hidden],(WEEKNUM(NOW())),Table2[Customer],"Supplier")) no longer subtracts the supplier value from the current week, number late, and I have no idea how to tell it to do that.
I think it is also worth mentioning that the previous week column draws its information from a "change history" worksheet which keeps track of every minute detail of a job. Where as the current week column draws its information from the "status log" worksheet which only contains current details.
I have attached a sample workbook.
Thanks for any and all assistance. Hopefully that explanation made sense.
Sample Workbook.xlsx
Bookmarks