Hello,
I have an inquiry. I'm not a newbie to Excel, but have a problem that needs immediate assistance. Help will be much appreciated.
I need to build a graphic with the amount of salse reps logged in per hour. My system is not able to do so, hence I need to calculate and draw it by week day on Excel.
Here's what the data I have looks like (see below for column assignments):
Date Start time End time Name of Sales Reps logged in
1-Aug 6:00:00 AM 7:00:00 AM A
1-Aug 6:30:00 AM 10:00:00 AM B
1-Aug 7:00:00 AM 8:00:00 AM c
1-Aug 8:00:00 AM 9:00:00 AM A
1-Aug 8:00:00 AM 9:00:00 AM B
2-Aug 9:30:00 AM 2:00:00 PM c
2-Aug 10:00:00 AM 11:00:00 AM A
2-Aug 10:00:00 AM 2:00:00 PM B
2-Aug 11:00:00 AM 12:00:00 PM c
2-Aug 11:00:00 AM 12:30:00 PM A
3-Aug 11:30:00 AM 12:30:00 PM B
3-Aug 12:00:00 PM 2:00:00 PM A
3-Aug 12:00:00 PM 1:00:00 PM B
3-Aug 12:00:00 PM 1:00:00 PM c
3-Aug 12:30:00 PM 1:00:00 PM A
3-Aug 12:30:00 PM 1:00:00 PM B
3-Aug 1:00:00 PM 2:00:00 PM c
(Column A is the date, B the start time, C is the endtime, F is the sales rep logged in.)
This data repeats on over 688 rows.
What I want is to build a few graphs once I have the number of concurrent sales reps logged in by hour. So If a rep is logged in from 6:30:00 AM -10:00:00 AM, he's to be counted as the rep logged in across 6:00:00 AM - 7:00:00 AM; 7:00:00 AM - 8:00:00 AM, 8:00:00 AM - 9:00:00 AM & 9:00:00 AM - 10:00:00 AM, net net he is to be counted in each hour along with the others logged in during this time..
What functions should I use to build this ? I need to extract the info using a function into a spreadsheet and THEN build the graph from that ? If so, what functions do I need to extract the data ?
Please advise....
Best,
Jai
Bookmarks