Solved

Grouping dates without a pivot  table?

Posted on 2011-03-19
16
264 Views
Last Modified: 2012-05-11
I thought I had  this projected completed, but it turns out that I did not account for overtime days.
Right now, I have my sheet set up to track the 5 - 24 hours work days in a pay period. The trouble is
I did not account for any extra days that might be worked in the pay period. The way our work cycle is
set up, it is possible to work a total of 8 shifts per pay period (unlikely). I can just add 3 more worksheets
and then continue to total it the way I do. But, it is already too "busy" on the home page, so my thought
is to collect all the data on one worksheet, thus avoiding 8 different sheets. That is fine, I could add a date column and
then just have one worksheet (which doesn't sound too bad).

My formula's are set up to add all the time for each work day (24 hours), then round based off the total for that day.
If I make it on one worksheet, I would have to somehow group the entries into each work date, so I could be able
to round my time based off of the date. I know how to group with pivot tables, but I don't think I can use a pivot
the way this is set up? or not sure if that would even be a good idea.

Look for suggestions on how I can set this up so I can collect this data on one worksheet.
Thanks expertcompile.xlsx expertcompile.xlsx
0
Comment
Question by:bvanscoy678
  • 9
  • 7
16 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35172178
You don't need a pivot table to get the data you need, but you can create a pivot table from multiple sources - see this reference/tutorial from contextures.com:  http://www.contextures.com/xlPivot08.html

I suggest that you may find value in using the SUMIFS formula to get the data needed for your report.  Just add a DAY column to one of your tracking tabs, and you then have a consolidated collection tab.  You can pull into there from the other tabs, or just collect your data in that place.  Direct links (as you have done) or using SUMIFS should help you to populate if you're trying to do the former.

You could pivot that, if that is your end-game.

Let me know if you need further assistance.

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35172324
I really don't want to do pivots.
I can create a date column and just do one sheet.
I can still do sumifs to collect the data, but not certain
on how to group the dates so I can keep totals for each date?

I will need to keep the dates separate, since I will be rounding  up the totals for each date.

Thanks for any guidance.

Brent
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172497
Create a simple example after you've consolidated to one sheet, and where you want the formula and I'll give you the example.

Dave
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
LVL 41

Expert Comment

by:dlmille
ID: 35173046
Can you elaborate what you mean by grouping the dates, if its more than doing a sumif on a date range?

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35173545
Sorry. Had to take the kids to games.

I have attached a simple layout. Basically, instead of having a worksheet for each day, I could just have all of my data on one worksheet.
Then I would like to have the =sumifs broken down for each date, instead of the entire worksheet. I guess this would be doing a sumif on a date range.
It would total for each date inputed. So, all of the hours for 2-26-11 would total together. All of the hours for 2-28-11 would total.

they would enter the date as part of their data input.  

The totals page would simply just be a sum of all the dates totals.

I hope this makes more sense.

thanks




expertcompile-part-2.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173707
It does - is this urgent?  or can I handle this later this evening?  (anniversary today so need to step away now that my wifes back home from hauling kids).

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35173728
Ok - I snuck back in right quick.  I think I get it.

Here's the formula in the first left top corner of the output block:  

=SUMIFS($I$5:$I$32,$A$5:$A$32,Q$18,$D$5:$D$32,$P20)

Its summing what's in I column, then checking the date range in column A, and comparing it to the date in column Q, then checking the type of pay in column D against the type in P20.  If you have more criteria ranges and criteria, you just keep adding them.

Then, I copied this formula down and across.

Check me on this, and my output is different than your sumif but think its correct.

Cheers,

Dave
expertcompile-part-2.xlsx
0
 

Author Comment

by:bvanscoy678
ID: 35173745
Okay, there was a #REF error in the R column, but I fixed that. So, yes this formula will work and that is good. Thank you. The only issue I have is this =sumif is based off of values
that I populated in Q18-X18. When the guys start to fill these out, it will be blank. They will just enter the dates in Column A. They won't enter dates into Row 18.

Some how I need to be able to have a  formula look at Column A and say, "these are the dates. Then take all the dates that are the same and do the sumifs for each date."

Does that make sense?

Thanks for you effort in this.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173899
Just change the cell reference.  I pushed it out to 5000 rows.  Also, I set the first date to the earliest date using the MIN function and the dates to the right are one more than that, thus daily.

Make sense on your end?

Dave
expertcompile-part-3.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173901
Will that work - or do you need a distinct set of dates in your summary?  e.g., could be dates all over the place...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173903
You could use a worksheet function to start with the nth largest or smallest and work your dates across like that, starting with MIN or MAX on the left side.

that would give you a distinct list, if they aren't daily from minimum date.

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35174104
lno, this is not urgent. I solve it when I solve it.

I am not following you on  the date part. Let me look again.

thanks
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35174134
You said "take all the dates and do the sumifs"  my comment is on that.  Load the file I sent you and see how I set the first date.  The subsequent dates are one day later each.  Is that how its going to be?  Folks reporting in for 1 week?  Or are these random dates?

Dave
0
 

Author Comment

by:bvanscoy678
ID: 35174988
Hey Dave,

Ok, I did miss how you did the dates. We report for 2 weeks and we work wierd schedules. We work every other day for 10 days, then have days off. Plus with overtime dates, it is going to be a random type of dates.

I am thinking of using a pivot for collecting the dates, then use your formulas to collect the data. The only draw back is
that the pivot will have to be refreshed. No major issue, but one I was trying to avoid.

Let me look again, since it is early!

thanks
Copy-of-expertcompile-part-38pm.xlsx
0
 

Author Comment

by:bvanscoy678
ID: 35175088
I am pretty close now, just need to make the pivot table grow, so it will see the new dates.

I'll check back.
expertcompile-part-4pm--1-.xlsx
0
 

Author Closing Comment

by:bvanscoy678
ID: 35175170
I used the pivot table to collect the dates, then your formula to complete the project.
Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

778 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