+ Reply to Thread
Results 1 to 16 of 16

Need to calculate the number of sales reps concurrently logged in by each hour of the day

  1. #1
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Need to calculate the number of sales reps concurrently logged in by each hour of the day

    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

  2. #2
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    This might take some work. Can you post the file? Also, if possible, keep a sheet with calculations and a graph done manually. You would want this seperately for each day right?

  3. #3
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Thanks Pooja. I'm not at my system so can do that once I get back.

    BTW, the data is similar to what I've provided below. It just carries on till row no 688.

    I also need to have this data first, the grarphs will follow.

    Look forward to your assistance!

    Thanks for all your help! Hopefully, I'll have this sorted.

    Best,
    Jai
    Last edited by [email protected]; 09-09-2012 at 09:46 AM.

  4. #4
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Pooja,

    I've the file ready but somehow do not see the upload option. Am I miising out on something, please help!

    Best,
    Jai

  5. #5
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Got it..Here is the file...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Jai,

    I have attached a temp file here. Please keep in mind it is not finished, just want to make sure that this is what you are looking for.

    I could not think of a quick excel formula for this, so I have coded the requirements.

    Please have a look at this sheet. It will run on any one day. Currently its running on Wednesday, but you can input any day and any number of observations for that day.

    p.s. is there an issue with the original data entry for J?
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Pooja,

    Thanks for your efforts.

    If I understand It correctly, the data is derived using a macro. Right? Let me admit, the problem here is that I'm not skilled enough to develop macros, though have good experience in pure excel. So I'm not sure, how this can be used...

    As far as the data in the tab labeled "Day table" is concerned, it definitely seems to be in line with my requirements. My understanding of the data is as follows:

    1). Rep R & N were the only 2 reps that were concurrently logged in between 6:00:00 AM - 7:00:00 AM. Correct?
    2). Rep M was logged for 5 slots between 9:30:00 AM - 2:00:00 PM (Slots were: 9:00:00 AM -10:00:00 AM, 10:00:00 AM -11:00:00 AM, 11:00:00 AM - 12:00:00 PM 12:00:00 PM -1:00:00 PM & 1:00:00 PM - 2:00:00 PM). Right?

    If the above cases are correct. I think I will have the results I'm looking for, but the problem would be w.r.t. the Macros. Unfortunately, I don't have experience on that. So an excel formula would best fit my requirements unless you think there is an alternate way i.e. If you could help with a readily available macro, that can be run on data in future as well. I'll need to do this exercise on a monthly basis....

    Thanks in advance!

    Please help....


    Best,
    Jai
    Last edited by [email protected]; 09-10-2012 at 12:47 AM. Reason: Formatt

  8. #8
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Macros are forever!
    As long as the data format and output requirements do not change, that is.

    In your reply you have said that you do this on a monthly basis. So does this mean that you want 30 tables to be generated every month. One for each day? Or something else?

    I think once the above question is cleared, I can make the final version for you. Actually even the graph making can be coded, if you could provide a sample made manually.

  9. #9
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hello Jai,

    I am not sure is this your requirement.

    Try this in D2,

    =COUNTIFS('Raw Data'!$E$2:$E$688,D$1,'Raw Data'!$D$2:$D$688,">="&$C2,'Raw Data'!$C$2:$C$688,"<"&TIME(HOUR($C2),60,0))

    Then copy down & across.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  10. #10
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Pooja,

    I guess I can't thank you enough!

    What I need is for each day say if there are 4 Mondays in a month then I need 4 for Mondays, like wise if there are 4 Tuesday I'll need 4 it and so on and so forth..

    As far as data requiremnts and formatt are concerned it cann be controlled.

    I can send you a formatt of the graph once the output is finalized. I haven't created one as yet!!

    I really appreciate you help!

    Thanks,
    Jai
    Last edited by [email protected]; 09-10-2012 at 10:47 PM.

  11. #11
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Good morning Pooja!

    Just wanted to check if you have any further queries.

    Look forward to your assistance!

    Thanks,
    Jai

  12. #12
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Jai, sorry for the delay... got caught up at work.
    Please find attached the file.

    It is working on the entire monthly data. Please keep in mind a few things before testing.
    The data HAS to be in the proper format, i.e. date as to be in date format and time has to be in time format.

    If your input data is in the same format as that you had given me, it should work fine.
    Please do not hesitate to call back in case it throws an error, it might develop some teething problems.

    Pooja
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Thanks a ton Pooja.

    Don't have words to thank you! I trully appreciate your help!

    I'll check this once I get to work today. BTW, I don't have your number and so was wondering where to callback.

    I also wanted to check something related to VB with you, if you could kindly let me know a nummber I'll be happy to call!

    Thanks again!
    Jai

  14. #14
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    No problem Jai, but I don't think exchange of personal information is a good idea in an open forum. These things are generally frowned upon by the moderators.
    Is "[email protected]" your email address? If any further discussion is to be done it had best be done away from this platform. I will mail you.

    In case your current query is resolved, we need to mark this thread as "SOLVED".

    Let me know...

  15. #15
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Ditto! Pooja. I completely agree. Its never a good idea to share personal info on such a platform.

    And, yes that is my email address, please feel free to email me there.

    As far as this query is concerned, I'll review the output once I get to office in a couple of hours. We can then mark it as solved.

    Once again thank you for all your help!

    This was my first post so apologies if there was anything that wasn't as per the rules of the FORUM.

    Look forward to your email.

    Best,
    Jai

    Excuse typos, sent using my mobil

  16. #16
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Need to calculate the number of sales reps concurrently logged in by each hour of the

    Hi Pooja,

    Thanks a ton. The query has been resolved to perfection!!! Much much appreciated...

    I'm assuming I need to mark it as "Solved" and so have done it.

    I'll surely look out for your assistance in future, and of course your email....

    Best,
    Jai

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1