Calculate number of days required by each person at any time on a date

Hi,
I am hoping for advice how to calculate number number of man days required on any given date by all the names together and by each name so that I can better resource plan, but I cant work out how to set the formula and was wondering if you could help me.

To explain the table below in the attachment (which is the feed to the question)

The start and end date are self explanatory, duration is the number of days between the dates (not interested in this question), man days is the number of man days per week that Name should be allocated between the start and end dates.

What I need (and I am expecting is two separate lists) to show is

i) a list by date showing that on any given date how many total man days will be required for all names
ii) that same list showing the number of man days requested for each person grouped by name within date

So I am looking for something like the following from each point

point i) would come out like the below

04/01/2016 5.0 days
05/01/2016 5.0 days
..
.. dates continue in the list
..
18/01/2016 9.0 days (made up from 5.0 + 4.0 from 18/01)
19/01/2016 9.0 days
..
.. dates continue in the list
..
01/02/2016 10 days (made up from 5.0 + 4.0 + 1.0 from 01/02)

etc etc

point ii)

18/01/2016 4.0 days Bob
19/01/2016 4.0 days Bob
..
.. dates continue in the list
..
03/03/2016 5.0 days Bob (made up from 4.0 + 1.0 from 03/03)
03/03/2016 5.0 days Bob
..
.. dates continue in the list
..
12/04/2016 4.0 days Bob (made up from 4.0)

etc etc repeated for Harry, Joe and the rest grouped by name

--
Hope this all makes sense, could anyone please help me?

I could of course change the table if that helps, but I am trying to make the table quick to fill and excel to do all the work, so I dont want a time consuming table to set up.

I'm not an expert in excel 2010 so simple clear advice would be appreciated

thank you

Rob.

Hi
Try this (suppose your table in A2:E9) and dates from A12
for total man days:

Formula:

Please Login or Register  to view this content.

for total man days grouped by name:

Formula:

Please Login or Register  to view this content.

See the file Lists.xlsx

Please post a sample file (not image) showing expected outcomes (manual calculations) as respondents do not wish to type in data to test any solution

To upload a file, click "Go advanced" then "Manage attachments"

Thank you.

Originally Posted by JohnTopley

Please post a sample file (not image) showing expected outcomes (manual calculations) as respondents do not wish to type in data to test any solution

To upload a file, click "Go advanced" then "Manage attachments"

Thank you.

Thank you John - I will do that - sorry this is new to me

Rob.😳

Ah - Jose Augusto - is A12 where the new table will be placed ? Is that where the individual date column will be starting from?

Hi
You can place the resume table in other place on same Sheet or in an other Sheet.
See the file Lists (1).xlsx
Regards

Jose Augusto. I am starting to get confused now I am afraid . I'll stop posting until I try it - I like having the table be able to grow as I add more entries on another sheet, but I have no idea what a resume table is, and whether there is extra code required above what you told me this morning .

Rob

Hi Jose Augusto,
I have now tested the formula you have provided and it works very well.

Would you be able to help me by updating it so that it adds 0 (zero) values as NA, so that the chart will not try to plot the zero values for dates we have not yet reached.

Many thanks

Rob

Hi

Use

Formula:

Please Login or Register  to view this content.

See the fileLists (2).xlsx
Don't forget to mark this as SOLVED if you consider it.

Thankyou Jose Augusto - thats Excellent. It is now Solved.

Not sure how to set it to closed, but it is now