Produce the highlighted area
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.xlsx
.
Days
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.xlsx
.Days
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
Question you accept VBA solution ?
Can you also post an example for the 2 other shifts ?
gowflow
ASKER
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.
I don't have anyone on those other shifts yet.
Basically this example calculates time not logged in between 9:30-6.
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
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
ASKER
That is correct. I get it exactly how it is pasted there. A2 would be ok.
ok then will be back shortly.
gowflow
gowflow
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
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
ASKER
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.
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.
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
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
ASKER
I'm confused about what your question is?
ASKER
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!
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!
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
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
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
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
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
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
ASKER
it is 100 percent up to you. Thank you for your help.
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
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
ASKER
Thank you. I look forward to it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding! Interestingly, clicking the standard box that says "enable macros" doesn't do anything.
ASKER
Wrong again, it works fantastically.
ASKER
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?
Sum doesn't seem to work for totals. What formula will give me two totals at the very bottom of the two purple columns?
I didn't do one but could include if you give me 1 to 2 hours as I hv friends over.
gowflow
gowflow
ok here it is. Sorry for the delay. Pls try and let me know if it is correct.
gowflow
TimeCalc-V01.xlsm
gowflow
TimeCalc-V01.xlsm
ASKER
There is an error on that one after clearing data and pasting updated data
Capture.PNG
Capture.PNG
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
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
ASKER
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.
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
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
ASKER
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.
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
gowflow
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