Hi Folks,
i have a crazy requirement
background of this requirement is
i am capturing the employee information on daily basis and each task wise irrespective of shifts
it means example
emp a shift starts at 8 am
emp b shift starts at 1 pm
emp c shift starts at 6 pm
capturing the information irrespective of login time
here for emp a hour 1 is 8 am to 9 am
for emp b is 1pm to 2 pm
for emp c is 6pm to 7 pm
so hour 1 can be anything( any time) ,
now here my requiremnet is i want see the hour1,hour2....,hour9 used tome for all employees
it means emp a will start shift at 8 am ,
08:00 am to 08:30 am meeting
08:30 am to 08:50 am away
08:50 am to 09:00 am is mailbox checking
like wise for all employess we have information
now i want to see all employee usage in hour 1,hour2....hour 9.
tricky thing is here:
if employee exactly ends hour 1 end time then we can easy calculate it but for some of the employees tasks ends like this
08:00 am to 08:30 am meeting
08:30 am to 08:50 am away
08:50 am to 09:10 am is mailbox checking
from abobe data mailbox task is falling under hour 1 and hour 2
so here we have to take 10 mins of time from mailbox and put it into hour 2. and 10 mins time to hour 1 ,
mail box task should be there in both hour 1 and hour 2
I am attaching sample data file to the link pls have a look and let me know ur thoughts
i have source columns like this
Team -team name (database)
Region -(india)
SPOC -(emp name)
Date -date
Start Time -starting time of each employee
End Time - ending time of employee
Time Spent (mins) - time spent
Broad Category - used for (like mails,meetings,away...)
attached my work application .....
