Advanced Problems in excel

[Lakshpraks]

Hi,
i have a shift schedule for a week and i need the following data from it:

1. I want to know the count (staffing) in time intervals.

For ex:
Monday:
01AM to 11PM - 2 Agents (count)
11PM to 6AM - 1Agents
etc till logout...


The data is here:

		Monday	Tuesday	Wednesday
		31st dec	1st Jan	2nd Jan
Emp ID	Name	Login	Logout	Login2	Logout2	Login3	Logout3
123456	John	12:00:00 AM	10:00:00 AM	12:00:00 AM	10:00:00 AM	12:00:00 AM	10:00:00 AM
78910	Paul	OFF	OFF	OFF	OFF	1:00:00 AM	11:00:00 AM
890123	Peter	1:00:00 AM	11:00:00 AM	3:00:00 AM	12:00:00 PM	6:00:00 AM	10:00:00 PM
987654	Adam	3:00:00 AM	12:00:00 PM	6:00:00 AM	10:00:00 PM	1:00:00 AM	11:00:00 AM
321654	Micheal	6:00:00 AM	10:00:00 PM	7:00:00 AM	11:00:00 PM	1:00:00 AM	11:00:00 AM

 

[ask_rocks7]

Here you go.....

Hi,

Although with the given format of the data, its very difficult to come up with reports that'd make sense.

I have recreated a sample data. (Me bieng from BPO background helps)

Hope this helps...

You'd have to change the format into a more vertical one as mentioned in the attachment

Then by creating a Multi-level pivot, you'd be better off getting reports as and when you need by changing a few variables

Let me know if this is not what you had in mind

Thanks

 

[Lakshpraks]

Thanks a lot for the reply,
Actually what i have a detailed description of what i require in the attachment, i request for your advice.

**************************

Hi,

Although with the given format of the data, its very difficult to come up with reports that'd make sense.

I have recreated a sample data. (Me bieng from BPO background helps)

Hope this helps...

You'd have to change the format into a more vertical one as mentioned in the attachment

Then by creating a Multi-level pivot, you'd be better off getting reports as and when you need by changing a few variables

Let me know if this is not what you had in mind

Thanks