Recording task in work durations

Question

I have a sheet displaying a list of tasks in column A, I then have a list of dates in column B onwards. I am then recording in each cell below the number of people assigned to each task on a given day:

Task	01nov	02nov	03nov	04nov	05nov
Abc	2		2	2
Def		3	3	1
Ghi		2	2
Jkl	1	1	1	1	1

What formula would I need to show what dates each task are in work? The what I am trying to illustrate is the duration each task is in work and not in work, the number of people assigned to each task on a given day is not important.

Thanks in advance for any assistance

Answer 1

Excel 365 Pro Plus with Power Pivot and Power Query.

With fictitious sub-tasks instead of number of people.

With Pivot Table and colored Gantt Chart.

https://www.mediafire.com/file_premium/cbzbmz38hhuat9q/11_26_23a.xlsm/file

https://www.mediafire.com/file_premium/bdkf395j0npa4ob/11_26_23a.pdf/file

Answer 2

Hi there

To better help you

Kindly suggest preparing and uploading a sample file to Onedrive, Dropbox, etc ... and share the link here.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany

Answer 3

I am recording task progress using a gantt chart template on excel. The data driving the gantt chart is coming from a different source to the data I have demonstrated above, that data is providing the task start date and the current status date of each task. However the tasks are not being worked every day, there are days when each task is being worked for consecutive days then days with no work being performed. What I trying to show is a gantt chart showing not only the date a task is started initially but also the days when the tasks are sat idle. At the moment my current gantt chart is showing task Abc starting on 1NOV with the latest update being 04NOV, giving the impression the task has been in work for 4 days, but in reality it was sat idle on the 2NOV.

Essentially I am trying to find a formula that will look at each task row and return the dates in the column header where a headcount number is entered. So I can find a way to plug that data into a gantt chart showing start dates, duration in work, duration idle

My apologies if that is not clear, I can't think of another way of describing it. I might also mention that I am not the most proficient with excel and am entirely self taught through youtube and web searches. I think what I am trying to show is best done using Microsoft Projects but I do not have access to this software.

Answer 4

Hi there

Re, "....What formula would I need to show what dates each task is in work?"

In the Picture below, Is that what you are looking for?

Please, give more details of your expected results

Regards

Jeovany

"

Answer 5

Hi,

Show the expected result clearly.