Calculating task duration, hours spent on task, remaining hours

[aronax_1]

Hey,

Need your help asap. It would mean a lot to me.

I have the following information:

- employees that work 20 hours/week;

- 100 hours/project;

- employees are assigned activities that have a start date and a due date;

- start date and completed dates are formatted as date, type 2014.07.13 14:00.

- several activities overlap each other

- I need help with a formula that calculates task duration, hours spent on task, remaining hours.

Help!


Thanks!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 2097"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Task ID[/TD]
[TD]Task List[/TD]
[TD]Milestone[/TD]
[TD]Task Name[/TD]
[TD]Task Description[/TD]
[TD]Start Date[/TD]
[TD]Due Date[/TD]
[TD]Priority[/TD]
[TD]Private[/TD]
[TD]Progress[/TD]
[TD]Status[/TD]
[TD]Assigned To[/TD]
[TD]Date Created[/TD]
[TD]Completed Date[/TD]
[TD]Completed By Firstname[/TD]
[TD]Completed By Lastname[/TD]
[TD]Parent Task[/TD]
[TD]Task duration[/TD]
[TD]Hours spent on task[/TD]
[TD]Remaining hours[/TD]
[/TR]
[TR]
[TD]1225050[/TD]
[TD]Inbox[/TD]
[TD][/TD]
[TD]Make google doc of all materials ordered.[/TD]
[TD][/TD]
[TD]11/07/2014[/TD]
[TD]18/07/2014[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]new[/TD]
[TD]Smith F.[/TD]
[TD]2014-07-10 16:09:53.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1049038[/TD]
[TD]Office Admin tasks[/TD]
[TD][/TD]
[TD]Sell surplus furniture on ebay[/TD]
[TD][/TD]
[TD]26/05/2014[/TD]
[TD]18/06/2014[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]completed[/TD]
[TD]Smith F.[/TD]
[TD]2014-06-04 09:53:11.0[/TD]
[TD]2014-06-20 17:32:50.0[/TD]
[TD]Smith[/TD]
[TD]Fendis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1076081[/TD]
[TD]Office Admin tasks[/TD]
[TD][/TD]
[TD]Sort out furniture in office[/TD]
[TD]Home Furniture | eBay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]reopened[/TD]
[TD]Smith F.[/TD]
[TD]2014-06-09 10:46:21.0[/TD]
[TD]2014-06-20 17:32:46.0[/TD]
[TD]Smith[/TD]
[TD]Fendis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1079949[/TD]
[TD]Office Admin tasks[/TD]
[TD][/TD]
[TD]Log incoming Invoices[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]completed[/TD]
[TD]Smith F.[/TD]
[TD]2014-06-10 10:03:34.0[/TD]
[TD]2014-06-20 17:32:45.0[/TD]
[TD]Smith[/TD]
[TD]Fendis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1085658[/TD]
[TD]Office Admin tasks[/TD]
[TD][/TD]
[TD] - Smith Move todo lists onto Teamworker[/TD]
[TD][/TD]
[TD]11/06/2014[/TD]
[TD]18/06/2014[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]completed[/TD]
[TD]Smith F.[/TD]
[TD]2014-06-11 14:51:12.0[/TD]
[TD]2014-06-23 13:24:36.0[/TD]
[TD]Smith[/TD]
[TD]Fendis[/TD]
[TD]1085654[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1085925[/TD]
[TD]Office Admin tasks[/TD]
[TD][/TD]
[TD]Clear Russells desk[/TD]
[TD][/TD]
[TD]11/06/2014[/TD]
[TD]16/06/2014[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]completed[/TD]
[TD]Smith F.[/TD]
[TD]2014-06-11 16:31:29.0[/TD]
[TD]2014-06-19 10:24:14.0[/TD]
[TD]Smith[/TD]
[TD]Fendis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

do employess only work monday to friday and only work 4 hours per day or could they work 7 hours one day and only one hour another day

you need a helper cell with = today() in it to give current date and time
it is then about checking if task is complete and was it finished early, on time or late
if not complete, is scheduled completion date less than =today()

also do all employees work same time eg 09:00 to 13;00