Solved

Produce the highlighted area

Posted on 2015-01-04
29
60 Views
Last Modified: 2015-01-07
The attached sheet contains rows and columns as shown.  The original export is "to clipboard" and contains data about when an employee logged into the phone and out again.  

We are trying to show logged out times to the right as well as totals for that day represented as minutes logged out and in decimal form rounded to one decimal place.

What makes this particularly tricky is that it must be based on the shift that the employee is assigned to work, such as 9:30-6 in the attached example.

My procedure will be to paste the data as shown with no modifications, select the appropriate shift from among 9-5:30, 9:30-6, and 10:30-7, and then run the macro to produce the values to the right.

Time in which the employee logged out, but did not log back in during the shift must also be calculated.  If they logged out at 4:30 and did not come back, then for 1.5 hours, they were missing and this time should be accounted forEE-Post.xlsximage.

Days
0
Comment
Question by:frugalmule
  • 16
  • 13
29 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I gues you need a calculation per agent (Col B) ??
If yes then do not understand why your first example you have for the second example the first part 0.47 is fine but what the 0.49 and 0.08 stand for ?

You need to check per agent code and per date right ?
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pls ignore my comment above as realized what it is. The cumulative time for the agent and the decimal part.

Question you accept VBA solution ?
Can you also post an example for the 2 other shifts ?

gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
I think I can accept VBA.  I havent tried yet.  If it is a xlsm, I should be able to run the macro.  Crossing my fingers.

I don't have anyone on those other shifts yet.

Basically this example calculates time not logged in between 9:30-6.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok don't worry I usually develop solution that are very easy to manipulate. and you are correct it is an xlsm file. One more thing
I understood from your post that you get this data somehow on the clipboard and you then do a Paste into an excel and this is how it comes out. in this format and in these columns

RIGHT??

If my assumption is correct then I will develop the macro in a workbook that we can call TimeCalc.xlsm and each time you want to make your calculations you open that workbook, I will have there 2 buttons the first to run the macro that will develop and the second to just cleanup the old data.

If this is ok for you then I will have 1 more question

You obviously pasted your data in cell A1. Would it be a problem for you to paste the data in cell A2 so we can use row 1 as heading and other things ?
this means you would have your data from row 2 and downward in the same format that you have now and exactly as you requested ??

gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
That is correct.  I get it exactly how it is pasted there.  A2 would be ok.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok then will be back shortly.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
One more
If the guy/girl/Agent comes in earlier ... like 9:26 ... that does not count right (no bonus for good people ?)

and then
the first Agent the result should be:
Name Agent      1311508            9:43AM            2:29PM      11/15/2014      9:30       9:43      0:13         14:29      18:00      3:31       3:44      3.7

I presumed came late a 9:43 so here goes the 0.13
then left at 2:29 for shift till 6pm then this is an other 3:31 for a total for that day of 3:44 in decimal 3.7

If all this is correct then given all this info how do you want to split them across ??? as then you would have for some data in columns that is not the same for others.

gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
I love your top comment.

Unfortunately, this place thrives on negative reinforcement.  I hate it and don't believe in it, but I stil have to provide the data wanted by my superiors which for this is simply the time that they were not logged in, if they were supposed to be.

I would prefer a 12hr am/pm format as opposed to military time.

I'm not sure I fully understand the question which is why I provided the screenshot.  In my screenshot, I do notice that I did leave out that 13 minutes late.  Good catch.  Also cell J2 would not be needed until the end of the day totals are calculated.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your screenshot as well as your file TOTALLY Ignored the first agent !!!

It seems you are blinded by the data.
The first line is for
Mr/Miss 1311508
which I requested clarifications.

Whereas your example highlight
Mr/Mis 1311504 for day 17 Nov 2014 only !!!

So please review the data for the first Mr and let me know.
gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
I'm confused about what your question is?
0
 

Author Comment

by:frugalmule
Comment Utility
They are all the same agent for this report.

Your data for the date of 11/15/2014 appears to be correct.

Name Agent      1311508            9:43AM            2:29PM      11/15/2014      9:30       9:43      0:13         14:29      18:00      3:31       3:44      3.7

I see that there are two items to represent on that one line.  I'm not sure how best to represent it but we do need each time span that they are not logged in, as well as the totals for that day.

Thank you again for your help, very much!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
The example you showed was on id 1311504 for day 17 Nov 2014
and that is fine.

now my question relates to the first item on the list of data that happens to be
id 1311508 for day 11/15/2014

So I showed you what should be the outcome of the macro or the result. As the Agent came late and also left late there is 2 times that are late for 1 single item which is in 1 row so how do we lay out the information on that row given that you had outlined the columns where you want to see the time come in late and out late and the difference and the total per day and the decimals. being form col H to L as per your example.

So my question is: in this specific case id 1311508 how do we lay down the information ?

Clear now ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
our answers crossed !!!

I guess you answered

and I conclude that I need to propose a way for you to see the data that make sense do we agree ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Question:

Am I allowed to organize the output that you need to see like from Col H to maximum right ?? or you need it in a specific way ???

gowlfow
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:frugalmule
Comment Utility
it is 100 percent up to you. Thank you for your help.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Noted and will do,

Meantime ...
as time zones surely are not all the same across the globe, it happens so that I am now in one where time is showing ... something close to 3AM which is not a bad idea to continue this ... the next day !

see u, then
gowlfow
0
 

Author Comment

by:frugalmule
Comment Utility
Thank you.  I look forward to it.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Yes no worry have been working on it ... all day !!! :) and with pleasure as I like new challenges.

I hope you will like what you see. Let me just brief you.

1) It is not in A2 that you need to paste new data but in A3 !!! we missed a row.
2) you have a dropdown from which you can choose the shifts. hv fun and choose the shifts even if your present data is from 9:30 till 6:00 but see how calculation would be different if shift selected is different.
3) you have 3 buttons their are self explicit. I suggest you don't do Clear Data as it will erase present data so you can paste new one (do this only when you have tested the whole thing with the current data and ready for trying new data)
4) I have tried to arrange for the results to be the more clear possible.

Let me know your comments.

Last but not least once you load the file make sure macros are enabled !
Enjoy

gowflow
TimeCalc.xlsm
0
 

Author Comment

by:frugalmule
Comment Utility
Outstanding!  Interestingly, clicking the standard box that says "enable macros" doesn't do anything.
0
 

Author Comment

by:frugalmule
Comment Utility
Wrong again, it works fantastically.
0
 

Author Closing Comment

by:frugalmule
Comment Utility
Unbelievable!

Sum doesn't seem to work for totals.  What formula will give me two totals at the very bottom of the two purple columns?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I didn't do one but could include if you give me 1 to 2 hours as I hv friends over.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is. Sorry for the delay. Pls try and let me know if it is correct.
gowflow
TimeCalc-V01.xlsm
0
 

Author Comment

by:frugalmule
Comment Utility
There is an error on that one after clearing data and pasting updated data
Capture.PNG
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry cannot comment on this as did not see what you pasted !!!!
could be a wrong format could be a different column this is a total different issue.

If you want simply post a new question related to this one saying it worked already but with 'post the new data' it does not. Then will look at what is different.

You did not reply to the other question I asked you. Prefer we finish with this one first before talking a new issue.
gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
The first file you sent works, so this is ok.  We will just stick with the first file.  What I was talking about adding in the new question I posted was the ability to calculate hours not worked if the person did not log in at all on a scheduled shift.  Also, the columns for your calculation are titled ln and Out where they should actually be titled Offline Start, and Offline End.  Also, if it was an approved company holiday, then offline hours do not need to be calculated on those days.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Lets stick in here to discussions about the issue of this question which is the file I posted. All explanation about the next question kindly post them in the new question so we keep threads clear and not confusing.

To go back to this question issue:
What do you mean by the first file works ? you means the second one does not ? and if it does not what is the problem exactly ? I do not answer here questions sake getting points but rather sake giving FULL WORKING SOLUTIONS and I do not accept something less that.

Kindly advise what is wrong with the second file I only added the totals as you requested and tried it here all is fine.

gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
I no longer have a need for the totals written into the macro. So that issue is resolved as far as i'm concerned.I would love some assistance with the second question that you recommended my opening.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
pls post the comment on the other question as I will discontinue monitoring this one. Whatever you wrote in here pls repost in the other question.
gowflow
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now