Collation of data

[hayden_pinto]

Question regarding collation of data


I have Collate data of tasks compleleted by 150 people.

Each person has to complete 2 or more tasks

if compleleted before the 3:30pm then it would be considered as "completed" and if completed after 3:30 then "completed late"

If the task is not submitted then it would be considered as "not recieved"


as of now i am doing this manually and tracking the tasks completed which is taking alot of time and need to automate this process


I have uploaded a file as an example

https://docs.google.com/open?id=0B_8gsqDH91vgbVRvSnNWcmRSVTZwMGhOYVBTUjdQZw


how can i automate this process throght Excel '07', Macros.


the data that is recieved is in "Data"

The collation that is currently being done is in "Report"


I need to automate the process of updating the data in the report sheet

 

[Luke M]

I'm not able to look at your workbook, but my initial thought would be to use a formula to determine status:

=IF(IBLANK(TimeComplete),"not recieved",IF(TimeComplete<TIME(15,30,0),"Completed","Completed late"))


and then use come COUNTIF functions to determine the totals for each status.