• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

Help adding more columns to my pivot table

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
Legolas786
Asked:
Legolas786
  • 10
  • 8
1 Solution
 
yo_beeDirector of ITCommented:
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
 
Legolas786Author Commented:
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
 
yo_beeDirector of ITCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Legolas786Author Commented:
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
 
yo_beeDirector of ITCommented:
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
 
Legolas786Author Commented:
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
 
yo_beeDirector of ITCommented:
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
 
Legolas786Author Commented:
ok i assume this is not possible then?
0
 
yo_beeDirector of ITCommented:
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
 
yo_beeDirector of ITCommented:
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
 
Legolas786Author Commented:
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
 
yo_beeDirector of ITCommented:
I just changed it on mine and it seems to work.


Copy-of-copy_Dummy4.xlsx
0
 
Legolas786Author Commented:
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
 
yo_beeDirector of ITCommented:
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
 
yo_beeDirector of ITCommented:
I was not given enough time to reply, but found a solution (I Think) after the asker asked to have the question deleted
0
 
Legolas786Author Commented:
Sorry my bad, i thought i would just give up :(
0
 
Legolas786Author Commented:
thanks buddy that sorted it :)
0
 
yo_beeDirector of ITCommented:
Never give up ;)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now