At my place of work, we receive plans to be reviewed for building permits. We receive a LOT of plans. By statute, we are supposed to review and either issue a permit or issue a denial within 30 days of receipt. To be fair to all applicants, we try to operate on a first-in, first-out basis. Where it gets a bit complicated is that architects today are apparently incapable of doing their jobs (and I are one, so I'm allowed to say that), with the result that over the past two years I don't think we have issued a single permit for a non-residential project on the first submission. We've had a couple that went through SIX rounds of revisions and, even then, we had to red-mark the plans before we issued the permit.
My problem: Some time back, in order to try to keep track of who should be next in the queue, I created a spreadsheet that logs when plans are received, when we send out a response, what our response is, and then rinse and repeat for resubmissions. Each project gets a worksheet, and the first worksheet is a master list that pulls from each project worksheet the date of the most recent action, and the current status. The idea was (and is) to be able to sort the master list so the ones that have been waiting the longest percolate to the top of the list. But my sort is broken, and I don't know how to fix it.
My sort looks first at status/most recent action (from a list in the sort criteria), and then at date. It does that, but there's a glitch.
For status, I have several possible categories: pending (for a brand new application), resubmittal, R&R ("Revise and Resubmit"), approved, denied, expired. We don't really need to keep track of those that have been approved, those that expired because the applicant never followed through (by law they expire after 180 days), or those that for some reason we may have denied outright rather than allowing resubmission. The problem is that the "pendings" need to be tracked chronologically with the "resubmittals." The way the sort works now, it lists ALL "pending" projects in ascending date order, then it lists all "resubmittal" projects in ascending date order. This means that a resubmittal that's three weeks old could be listed below a pending that's only one or two weeks old.
If there are any Excel gurus in the audience, can you help me fix my sort so the "pending" and "resubmittal" entries are considered together when sorted by date?
Thanks
