|Sorry I know my first post was a garbled mess. here are 2 files that show the data that is imported (a very small subset of the data is included).... import.xls and the final report (what I would like the finished one to look like) Report.xls|
Step by step process to get it to Finished.xls
[B]1.[/B] rename tabs to "Original data", "Completed","SDI work in Process", and "Apps in client sign-off"
[B]2.[/B] sort all data on Original data tab by date
[B]3.[/B] Copy any data for current month to completed tab (this becomes our working tab)
[B]4.[/B] Sort data on Completed tab in this order 1. Application Name, 2. Activity Date 3. Workflow Major
[B]5.[/B] go back to Original data and do same sort, ensure you have all rows of data for all listed applications in Completed tab (look for rows that fell out of the original sort by month), if any extra rows found copy to Complete tab. In my example this would be rows 14 through 16 (Lotus Notes 8.5.2 Fixpack 2 (FP2SHF75) Basic) as this Application was started prior to 6/1/2011
[B]6.[/B] Verify the last (latest step) for each application, If last step = "Package Complete Phase" leave on Complete tab, If last step = "Client Signoff Phase" copy all rows for that application to the Apps in client sign-off tab, If last step anything else move all rows to SDI Work In Process tab
[B]7.[/B] Now all further work will be done in the Complete tab.....
[B]8.[/B] Highlight The Workflow Phase Complete, Request Phase, Yellow
[B]9.[/B] Highlight The Workflow Phase Complete, Package Complete Phase, Yellow
[B]10.[/B] Highlight any rows that are SLA Clock Stopped, or Client signoff, Red
[B]11.[/B] For each program listed (there is only one in my example) we need to find total time in days spent on project.... Total time = Request phase complete (line 4 on my example) to Package complete phase (line 16 on my example) minus any time spent in Client signoff phase (Lines 14 and 15 on my example) and any time spent in status of SLA Clock Stopped Formula "=NETWORKDAYS(+E2,E16)" gives total time spend, I then run the same formula on the clock stoppage and client signoff and manually subtract these from the initial total work time.... In the example total time worked is 16 days and total time to be subtracted is 5 days for a grand total of work time of 11 days
[B]12. [/B] Enter Final work time in cell next to total time
[B]13.[/B] If time is less than or equal to 13 days enter Yes in next cell.
[B]14.[/B] create headers over last 3 columns, F g and h on my example, Time, total time and SLA Met
Wish list... If possible the script/macro etc should prompt for date range, or month (either is 100% fine).
Thank you in advance