Solved

Help adding more columns to my pivot table

Posted on 2016-10-21
19
28 Views
Last Modified: 2016-10-24
hi

Ive had a little help in creating a pivot table on my excel workbook but could do with a little more assistance before its completed.

 I will attach my dummy excel file hopefully someone can take a look and help me with the last two columns? the excel file is pretty self explanatory

The columns i require help on are time before break and time after break

between hours of 12:00 and 14:00 on each day there is a break of around 1 hour so 01:00:00. Just like with the first time and the last time on each day i wish to state the time before the break and the time after the break, in the attachment I have the pivot table completed without the last two columns
Dummy.xlsx
0
Comment
Question by:Legolas786
  • 10
  • 8
19 Comments
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
what is your duration column for?
What is each time entry for each share group?
I read is as if each entry is either and in or out and the Min is the first in and Max is the Last out.  I hat correct?  
With that I am not sure what you are looking to calculate.   You mention 12 to 14 is a break period for about 1 hr.  I see Share1 has a 12:20 time and a 13:35 time. if that is the case I read as 1:15 or 1.25 hours that the person was on break for 1/1/2016, On 1/2/2016 I see a lot more breaks.  Is that correct.


Also your example table seems to have Before and After examples that do not match the source table data.
I see Share1 on 1/3/2016 have four entries.

There seems to be information missing from your question.
Please expand more if possible on what you are looking  for.
0
 

Author Comment

by:Legolas786
Comment Utility
hi,

I will do my best to explain

I currently have data in four columns

Column A (Name) will only ever contain two users in this scenario Spare1 and Spare2 Column B (Date) Column C (Time) Column D (Duration)

Currently it looks like this

first
I was looking to use VBA to achieve the following but was told it would be easier with the pivot table which is used in the dummy file

second
So the pivot table contains a record for each spare user, each date, the first time, the last time, the number of times recorded, and each duration more than 00:05:00 - this part is all working as you can see in my dummy text file.  Now i just wish to record the times between 12:00 and 14:00 for each user on each day where there is a break of more than 1 hour so to calculate each time between 12:00 and 14:00 for each day and if it is greater than 00:01:00 then to record these - as per my example below.

result
Hope that makes sense.
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
It is a bit clearer, but I still do not understand what the duration column is for and it may not be needed for this.  Since it does not seem to come into play in your question I will ignore that column.

If I build my own Pivot Table and see times that are grouped under Spare1 that do not match what your example desire outcome.

  img1
I am not really understand what each record stands for. Do you want to completely drop values that are outside the 12 - 14 window?  I have attached a modified excel file that will show you what I did.  
Copy-of-Dummy.xlsx
0
 

Author Comment

by:Legolas786
Comment Utility
hi

I'm sorry but this is my fault as i was manually entering the fields in the time before break and time after break they were incorrect.  Please see latest attachment and this should be correct now.

Also, I do need the duration column as that counts each time that is higher than 00:05:00 and displays this in Sum of >5 column.  A quick overview of each columns in the pivot table

Name      = shows the user so either spare1 or spare2
date      = shows each date
Min of time2      =shows the first time of the day
Max of time2      =shows the last time of the day
Count of time      =shows the total numbers of times in each day
Sum of >5      =similar to count of time column but shows total number of times that are higher than 00:05:00
time before break =should show first time during 12:00 to 14:00 windows if break more than 1 hour      
time after break =should show last time during 12:00 to 14:00 windows if break more than 1 hour
Dummy.xlsx
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
Is this what you are looking for?

Please see my Worksheets Tab REWORKED.  
I added a column to the table that is the source of the data.
=IF(AND(M3>=TIME(12,0,0),M3<=TIME(14,0,0)),M3,"")

Copy-of-Dummy.xlsx
0
 

Author Comment

by:Legolas786
Comment Utility
hello,

please see my dummy file attached.

If you look at spare2 for date of 05/01/2016 there are three times recorded within the 2 hour period of 12:00 to 14:00.

These are 12:15 12:30 and 13:45, the two times that should be picked in the pivot table are 12:30 and 13:45 as these are the closest times (within the 12:00 and 14:00 period) and over an hour however the pivot table shows the times as 12:15 and 13:45? would it be ok to change the layout of the pivot table slightly? this is in the dummy file.  Many thanks for your help
Dummy4.xlsx
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
you have way to many variables in play here.
You stated in a previous reply
time before break =should show first time during 12:00 to 14:00 windows if break more than 1 hour      
time after break =should show last time during 12:00 to 14:00 windows if break more than 1 hour
By this statement my formula meets your requirements.  This variable of three entries between 12 & 14 now you are asking something a bit different than your previous reply.
0
 

Author Comment

by:Legolas786
Comment Utility
ok i assume this is not possible then?
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
It probably is, but i need to work the formula to build the data to meet your requirements.

I see if I can work on this
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
I got the formula that works for your data table

IFERROR(IF(AND([@Name]=A4,[@date]=B4,C3>=TIME(12,0,0),C3<=TIME(14,0,0),TEXT((C4-C3),"h")>="1"),C3,""),IF(C3<=TIME(14,0,0),C3,""))

Open in new window


Above is the modified formula for the Breaktime column.
copy_Dummy4.xlsx

Let me know if this works
0
 

Author Comment

by:Legolas786
Comment Utility
hi Yobee

First i want to apologize for the late reply as I have been away from my computer for the last couple days.

When i was looking at my data I found that the time should be changed from 12:00 to 15:00 rather than 14:00.  So on your formula I made the following changes:-

=IFERROR(IF(AND([@Name]=A3,[@date]=B3,C2>=TIME(12,0,0),C2<=TIME([b]15[/b],0,0),TEXT((C3-C2),"h")>="1"),C2,""),IF(C2<=TIME([b]15[/b],0,0),C2,""))

Open in new window


The result that appear are strange, column F (BreakTime) appears to only display the first time before the 1 hour break and not the later time? any ideas why? many thanks
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
I just changed it on mine and it seems to work.


Copy-of-copy_Dummy4.xlsx
0
 

Author Comment

by:Legolas786
Comment Utility
ok thanks I tried your formula but it has the same results, i really dont know why but my on mine its only picking up the first time? i will attach it and hopefully you can spot something that is causing it not to work?
DummyLatest.xlsx
0
 
LVL 21

Accepted Solution

by:
yo_bee earned 500 total points
Comment Utility
Here is what I needed to do.
We needed a AND(or  statement,

=IFERROR(IF(AND(OR(TEXT((C3-[@time]),"h")>="1",TEXT(([@time]-C1),"h")>="1"),[@time]>=TIME(12,0,0),[@time]<=TIME(15,0,0)),C2,""),"")

Open in new window


copy_Dummy5.xlsx
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
I was not given enough time to reply, but found a solution (I Think) after the asker asked to have the question deleted
0
 

Author Comment

by:Legolas786
Comment Utility
Sorry my bad, i thought i would just give up :(
0
 

Author Closing Comment

by:Legolas786
Comment Utility
thanks buddy that sorted it :)
0
 
LVL 21

Expert Comment

by:yo_bee
Comment Utility
Never give up ;)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

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

15 Experts available now in Live!

Get 1:1 Help Now