Excel formula help

Hello, I have an excel spreadsheet with a great deal of data.  I'm looking for a few clean formulas to make the process better/work.  

there overall report is about 8000 line items detailing task assignment.  The current spreadsheet includes: Department, Task assignment date, task compeltion date.  Please note the task is due 20 days after open.  

So, What i need to pull is a report/sheet with the following columns of information:

Department  
-Number of Task by Department  
-Number completed by due date  
-Number still open (no completion date)  
-Number NOT competed on time (includes those that were compelted after the 20day due date and those not completed at all(no completion date))
-Percentage of tasks completed late (includes not compelted)
LadyHagoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Can you post an example workbook showing the layout of your data to enable us to better understand your requirements?
0
Patrick MatthewsCommented:
Howdy Wayne! :)
0
Danny ChildIT ManagerCommented:
Here's a cobbled-together summary of what I think you're after.

I've done the Department Count
Not Completed count, and
Completed On Time count.

presumably for the Not Completed On Time count, you'd want to EXCLUDE projects that were still within their 20-day limit?

I also made an assumption that you only work Mon-Fri in all date calcs.
M--Personal-ee---task-schedule.xlsx
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Danny ChildIT ManagerCommented:
Will try and stop by later, but gotta go now...
0
Danny ChildIT ManagerCommented:
I also set all dates in a UK format - ddd dd-mm-yy

 - you may want to change this if your location's different...
0
Danny ChildIT ManagerCommented:
found some time, try this:

In Col F - "Actual", you'd have the formula set for all new tasks.  Then, overwrite this with the final completion date when it happens.

"spare" copy of the formula in cell F24, in case you ever fill the table with dates...!
M--Personal-ee---task-schedule---v2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny ChildIT ManagerCommented:
Hi, is there any update on this?  Thought I'd covered all the bases here, pretty much???
0
pcelbaCommented:
This is purely database task so if you connect your sheet to any SQL database then you may execute a few simple queries:

-Number of Task by Department
SELECT Department, COUNT(*) FROM YourDataTable GROUP BY Department

Open in new window

-Number completed by due date
SELECT CompletionDate, COUNT(*) FROM YourDataTable GROUP BY CompletionDate

Open in new window

-Number still open (no completion date)
SELECT COUNT(*) FROM YourDataTable WHERE CompletionDate IS NULL

Open in new window

-Number NOT competed on time (includes those that were completed after the 20day due date and those not completed at all(no completion date))
SELECT COUNT(*) FROM YourDataTable WHERE CompletionDate IS NULL OR CompletionDate-StartDate > 20

Open in new window

-Percentage of tasks completed late (includes not compelted)
You may simply divide the number of tasks from the previous step by total number of tasks. 
I would also recommend to exclude not completed tasks before 20 days period

Open in new window


Of course, some of the above calculations would need clarification or fine tuning. Not all SQL engines support simple date subtraction. Etc.

If you need to report everything by Department simply add Department to the command as you can see in the first SQL example.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.