Adjusting Networkdays outstanding for future dates

**Moondog** · 03-24-2010, 01:51 PM

I am constructing a spreadsheet showing work team performance on a helpdesk system; data is extracted from the database as a .csv file which I then import into Excel. This particular sheet shows jobs by category with each job on a row and a column for the date the job was submitted. The rows are ranked by the age of the outstanding job, oldest at the top. Embedded in the heading I have the =TODAY() function, and another column uses the NETWORKDAYS function to calculate the number of days the job has been outstanding by subtracting 'date submitted' from =TODAY(). In another part of the spreadsheet is a summary box in which the outstanding jobs are broken down into categories; 1 -3 days, 4 -7 days etc. And I've set the workbook up so that as soon as I paste the .csv file onto Sheet1, the all-talking all-singing report with extra parmesan on Sheet 2 updates automatically.

The problem I'm having as that some of these jobs are 'booked in advance', so that while the job may have been submitted on 19th January it isn't required to be carried out until 10th June. At the moment these are counted in the summary box as outstanding jobs which is obviously incorrect. I have a column which identifies those jobs with a forward date and am trying to find a formula which will subtract the number of 'forward' jobs contained in each outstanding category from the total of that category. So if the summary box shows 69 jobs outstanding for between 10 and 15 days but 43 of those jobs are not yet due to be carried out, the total will return a value of 26. I can't just manually add them up and make a manual adjustment because eahc day when I paste the .csv file the position of jobs which fall into the e.g. 10 - 15 days category will change. I suspect the answer will be in the form of a nested IF function which will look at the difference between the forward date and the =TODAY() and ascribe a value of A to all jobs with a forward date that were submitted between 10 and 15 days ago, a value of B to those between 7 - 9 days and so on. It would then be simple to COUNTIF(A) and subtract that value from the summary total. My trouble is that,try as I might, I can't find the right syntax.

Hmm, yes I know. I understand what I mean,but might be pleasantly surprised if you do from the foregoing! However, if you do I'd be grateful for any help. And if you've stuck with the story this far, thanks for listening!

**arthurbr** · 03-24-2010, 03:47 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Moondog** · 03-25-2010, 08:56 AM

Arthurbr, thanks for that - I didn't think of actually attaching the thing, doh!

It's attached here - I've taken out the assignees names but otherwise it is my working copy.

It works up to column I, which is the jobs 'booked in advance'. At the moment cells D5 to H5 show the breakdown of outstanding jobs by time period, but the figures in these cells include the jobs booked in advance, which cannot be right. Taking jobs 4 - 7 days old as an example, what I am unable to work out is a formula which will identify the 'booked in advance' jobs within the 4 -7 days part of column I and subtract this from the total shown in F5. It seemed like a simple thing to do when I started, and would be if the data was static, but of course the position of jobs within the 4 -7 day categoryin column I will change each time I download a new .csv file.