Concurrent Reps by Hour & Net New using VBA - Solution Modification Request

Hi All,

I’m looking for some help on one of the reports that I need to prepare daily. I already have a formula solution in place, but due some recent changes in the structure the solution has become redundant, and consumes more than anticipated time.

In a nutshell, here’s my request:

I want to be able to calculate the number of reps that our logged in by Each hour of the day by Category (please refer to the attached sample report)

- Time slots are given (by hour) in Column O
- Categories are given in Column U

The existing formulas are actually calculating on overall basis not by category and not by the required time that needs to be used by Category.

My calculations are to be represented as depicted in Monday, Tuesday tabs. I need to run this exercise daily, which makes me lookout for a VBA solution.

The "Initiation Time" for calculation from the report for different categories to be used is as follows:

A & B – Column J is used for the Initiation Time calculation
C, D and E – Column T Click Hrs is to be used as the basis for the calculation
Actual Out Time remains the same Column H as the basis for all categories

The above time columns need to be formatted using a ceiling, as in my sample.

I’ve attached a sample report using the current solution (only calculates based on Column H and Column J without categories), and how the new data is to be presented in the two sample tabs (the data isn’t populated)

I would really appreciate your help on this. Please let me know if you have any questions.

Thanks,
Jai

Hi Experts,

Could you please assist me on this.

Thanking in advance!

Best regards,
J

Bump!

Could anyone please look into my request please....

No response. Please advise. Thanks!

Seems this will not get any assistance! Clsoing it as "Solved"

Vistors of this thread, please be advised that this is unsolved...

Hi Jai,

as per your private message I have given it a go.

what I have done:

- create a VBA routine which receives the weekday as a parameter, so try "Monday"

- For each rep row on the Master File worksheet where the value in column "L" matches the parameter

- calculate the starting hour by looking at the hour component of column J

- calculate the ending hour by looking at the hour component of column H and adding the value 1 if the minutes of column H are > 0

(the above part needs tweaking based on how you want to assign the timeslots)

- loop for the start to end hour that the rep is present
- increment the concurrent column of the correct category on row (hour -4) so (7 a.m. becomes row 3)
- if the hour of the time slot = start hour then increment column "Net New" for the category else increment column "Net Rollover"

Thanks OllieB!

Thanks a ton for your positive response. I agree to your point may be its the way I had placed my request that did not help me in anyway.

However, I’ll be glad to put everything clearly for you again. In the interim, I downloaded the file but couldn’t run the Macro.

Also when I looked at the Monday tab for the data I found that the 1:00am slot had got 2 in the concurrency field which isn’t the right. For better understanding, I’ve created another tab “Monday (JV)” which has my required result.

Please note that columns I and K in Master File are converted using the formula. The values used are from H and J. Values from I and K are then manually pasted by me in M and N.

Happy to provide additional details. Please let me know…

Thanks a bunch!

Jai,

You cannot run the macro by clicking on it, as you have to provide a PARAMETER (i.e. the weekday), and I had already mentioned that the timeslot allocation would require some tweaking as your requirements were not clear enough.

I do not really care about the formula's because it is quite easy to determine the timeslots using VBA. What I do not understand is the values in the columns H, I, J, K etc

I can understand there is a need to record the time the rep starts and a time when the rep logs out, but I cannot see which columns are which. The column headers are even more confusing.

If column J is the start time and column H the end time, then how can H be < J as shown on row 2 of your master file.

If I look at your new worksheet "(JV)" you list 12 concurrent for category A in timeslot 09:00, which I understand as 12 reps of category A being active between 08:00 and 09:00 or between 09:0 and 10:00 (you have not explained on how the timeslot is to be interpreted). But there are only 6 reps entries for category A in the Master File sheet, how do you get to 12?

Dear OllieB,

Point 1: My source data is in columns H (Actual Out Time) and J (Initiation Time)

The others (I, K, M and N) are merely helper columns that I use to run my formula solution. (Columns I and K are the columns where I use the formulas to convert time – as you suggested these can be easily eradicated using VBA)

Requested Time conversions methodology I follow (as you would have seen in my formulas):

0-14 mins previous hour which means that any time between 6:00am to 6:14am in column H is currently converted to 6:00am in Column I and likewise. But anytime from 6:15 to 7:14 is changed to 7:00am and so on and so forth…
0-44 mins previous hour which means that any time between 6:00am to 6:44am in column J is currently converted to 6:00am in Column K and likewise. But anytime from 6:45 to 7:44 is changed to 7:00am and so on and so forth…

Columns M and N merely have values in them.

Point 3: H < J as shown on row 2 of your master file. – This is an import bug. I’ve checked. It should have been 6:45:00 AM. Sorry about it!

Point 4: The 12 are actually for 08:00am – again an import bug and not 9:00.

Reasoning:

Anybody who logged in @ 8:00am or prior but was logged in even after 8 should be calculated as Concurrent

(in this case 1 logged in at 6:00am and was there until 2 PM, 5 logged in at 7:00am and were there – 1 until 12:00pm and 4 until 1:00pm while 6 other logged in at 8 and stayed beyond 8:00am) – Hence the requested output is 12 ignoring M2 & N2 where both times are same)

Anybody who logs in at @ 8:00am is net new
Concurrent – Net New = Net Rollover
Any entry with same Initiation Time and Actual Out Time can be ignored along with the one you pointed out in row 2. If that does happen again.

Hope the above helps me explain better. I’ve attached the import again.

Jai,

Lets break things down into components as it is all still not making sense to me:

If I select all entries on the Master File worksheet with a value "A" in column U, I get 6 entries. Of these 6 entries 3 have J < K (start time < end time) but 3 rows have J > K. So again, my question is, which column is the log in (start) and which column the log out (end)???

In your example data for category A you only have entries for timeslot 08:00 (is this 08:00 till 09:00?) and you mention 12 concurrent. As you only have 6 rows of category A I still do not understand how you can show a value of 12, and as these 6 entries also refer to different timings, I do not understand why you do not have any entries for the other timeslots for category A.

Your are absolutely right OllieB. Its better if we break it down. It will make it more clear and aviod any disconnects.

Yes, on filtering there are only 6 enteries. My apologies - I completely missed that step. My bad and apologies for this mix-up.

Column K is the start time and Column I is the end time.

Yes, all need to be hourly slots. 7-8, 8-9, 9-10 etc...

Would you suggest to keep us going lets assume that there are no categories for now and later we can customize / integrate them?

Guess taking small steps on this one would be the ideal thing to do.

Sorry again

Jai,

Taking it one step at a time:
column K is start time - which is basically column J with the logic of 0:44 minutes - OK - have that
column I is end time - which is basically column H with the logic of 0:14 minutes - OK- have that

This does however not explain those rows where K > I, such as for example row number 19, 20 and 26

Using the category is really not a problem, so lets leave that in for now

So it we look at only category A and only for rows 2, 3 and 9 we have two reps starting at 7, so I would get concurrent = 2 and Net New = 2. As the one on row 3 continues working till 09:00, I have one concurrent showing for timeslot 8 and the value 1 in roll-over. At 09:00 I have one new rep starting (row 9) and one still active from row 3, so two concurrent, one new and one roll-over

check the attached workbook

Thanks a ton OllieB.

I'll take a fresh look at it and comeback asap.

Best regrads,
J

So we are using original source data columns as the basis, and my helper columns will no longer be required . I can delete all of those. Please correct if I am wrong.

I've seen the report everything is flowing smoothly.

Just 2 things that need your expertise:

Row 2: I & K have the same time as 7:00:00 AM these cases should be ignored in the calculation as they technically do not meet the report criteria. They start and end at the same time.

Secondly, I'm assuming this is already being taken into account:

If a rep starts at 6:00PM and ends at 10:00PM - so the calculation will be 1 per hour slot (6-7,7-8,8-9,9-10)

You rock sir! I'll quickly have my dinner and login in to my system. On the phone now...

Sorry again for all the trouble. There a lot for me to learn!!!

Jai,

- correct: original time columns are used and helper columns are not needed - take care though that column U is considered to hold the category code
- correct: if start time is 7 and end time is 10 then all time slots in between are considered - we need to look at whether to exclude the entry for the last time slot or not - please test
- I changed the code to only process rows where end time > start time (so ignore if both are for example 07:00)

new version attached

Having thought about it some more, a correction was needed to prevent it from counting one slot too many. When posting your next query/remark please let me know which version you used to avoid cross-posting.

Dear Ollieb,

I must say you are as fast as lightining!

I'm now reviewing the output from Version 2.3.

J2 = 6:45:00 AM which is to be converted / is getting converted to 7:00 am. Right?
H2 is already 7:00:00 AM

So this should not be counted. What I'm left with is sir Row 3 entry which is 7:30 to 8:30. The Monday B9 (7 - 8 slot) is showing a value of 2. It should be 1 unless I’m missing anything. Please advise.

Thank you!

Strange. I am only getting a value of 1 as expected/desired. Try the attached version

Yes, this is superlike. It is covering this logic as well - Example: 7:00 clock slot can only cover those who started at 7 or prior but continued even after the clock hit 7:00. In other words the ones who started prior to 7 must not end at 7 or earlier.

For my usage purpose: I'll delete the data in all the helper columns and keep pasting my daily data in H, J, L (Weekday) U (the 5 categories that I have). I'll need 5 tabs then in the same manner that I have for Monday. How will that work? Could you please guide me on it...

I'll then start testing my humungous data and come back by nextweek (Max) Tuesday in case there are any probs.

Your are the best sir!!!!

Hi Jai,

Your copy & paste action to get the data loaded sounds complicated to me. Why don't we change the columns to:
A: Weekday
B: Start/In
C: End/Out
D: Category

Or would that be more difficult for you? Do you want to process the data one day at a time and the solution should look at the first data row to determine the weekday, or do you want to process the data for all weekdays in one go and have the solution check the weekday for every row and update the correct worksheet accordingly? I do not have a preference for either, so it is up to you.

Dear Sir,

Let's go with what you have suggested Column A to D. I can move rest of the data in my sheets from Column E onwards.

Regarding processing - I think option 2 is better. Processing the data for all weekdays in one go. I guess this will give me flexibility to run my reports (historic ones as well). The daily ones will in any case be taken care of.

Lastly, I divulged deep into the data last night and found that for one of the categories (E) the time available is in IST so that category will need an additional adjustment of 1.5-hours (subtraction - 8:30am IST = 7:00am PST ) to convert into PST before processing. The rest categories will have it in PST so no probs there.

Thanks a bunch!

One more thing is it possible to create 2 columns that will have the converted time. It will enable me to monitor if everything is processing smoothly and there are no issues in the source data, as we found yesterday that led to a disconnect.

All I want to ensure is I have a little bit of visibility and can check what is happening. Though I know once the programming has been set noting can go wrong unless the data is a little here and there. Your solution is awesome its just the data I'm a bit weary about. Please let me know your thoughts...I just want to have a QC parameter in place (if required)

Thank you!

New version attached. I have fiddled with the test data in the Master File (corrected the entries where end time < start time by swapping them around). Added the PST processing, re-arranged the columns

Thank you, thank you!

I'll run it in a bit. Absolutely sure it will be magical:-)

Have a great weekend Sir!

Magical is the word. I've tested it and at this point every thing seems to be alright.

One modification which I think is needed is when I remove the daily / weekly data from the master file the weekday tabs still show the counts. Is it possible to add a trick to clear the data prior to every run? Hope this makes sense...

I will then add two buttons on the Master File one to clear data from the weekday tabs before processing every time and another one to run this awesome piece.

Lastly, one question the categories currently defined are A, B, C, D and E. In case, these need to be changed to their original naming conventions. Are there any specific points that I need to keep in mind or are these generic? The # of categories remain unchanged.

You've made my Saturday, so much so that I've spent half of my weekend admiring and testing this.

Look forward to your final comments, and then I should be able to go ahead and close this thread as "Super Solved"

Best regards,
J

Jai,

new version attached:

change log:

1. All "weekday" worksheets are now cleared prior to processing the data from the Master File

2. QC columns have been added (E & F) showing the actual hours used to count concurrent etc (after all corrections to the times have been made)

3. QC column G has been added showing processing status/message ("OK", "INCORRECT TIME", "MISSING CATEGORY")

4. Category names - and number of categories - have been made flexible - as long as they are defined on row 1 of the Monday worksheet all non-blank values found will be considered a category and allocation will be dynamic

Mind blowing!!!

I'll run it now and comeback to close the thread.

You are simply amazing sir!!!

Thanks a ton:-)

Absolutely Phenominal Sir!

Thanks a ton for all your help on this assignment! I've tested it, and everything seems to be flowing smoothly now.

I don't have enough words to thank you!

Going ahead and closing the thread as "Solved".

Best regards,
J

Hi OllieB,

I’ve attached the existing solution provided, which is working superbly.

Unluckily, I’ve to add another piece (Table 2) to facilitate some analysis for each weekday. Here’s what the logics need to be:

In the Master File:

• Time In for Table 2 is the same as for Table 1 with the same logic of 0:44 minutes
• Another Column D with Time Out 2 is to be added and is to be used to facilitate calculations for the same categories in Table 2 of each weekday
• Time Out 2 Column D to be based on the logic of 0:14 minutes as in Timeout1
• Add QC Columns for Table 2

I’m open to any suggestions that maybe easy for you to incorporate given that the existing one is working absolutely great…

Please take your time. I’ll stay tuned…

Regards,
J

Jai,

try

Truly, I do not have words to express how thankful I'm.

Thank you very much sir. As always, its going to work like a charm

Thanks again for taking out time from your busy schedule to assist me further on this. You've really saved a lot of time and effort that I would have otherwise invested. I'll run it on my original data tomorrow morning.

Best regards,
J

Dear OllieB,

Sorry...My bad. Please ignore the attachment. It is absolutely working fine.

Just realized the backend calculation is further converting the time per defined logics.

Regards,
J

Jay,

Row 3:
Time In 08:00 -/- 1.50 hours for PST conversion = 06:30 - minutes are not > 44 thus hour is set to 06:00 (minutes re removed without adding an hour)
Time Out 10:00 -/- 1.5 hours for PST conversion = 08:30 - minutes > 14 thus one hour is added and minutes are removed thus 09:00

I believe this is as per your initial requirements (i.e. not changed since initial validated version)

Yes, that's correct. Our emails crossed each others. I've updated the last thread. My bad sir. Realized it a bit late, but the good thing is I realized when I revisted the Macro.

Sorry about the inconveneience...

Regards,
J