Solved

Grouping dates without a pivot  table?

Posted on 2011-03-19
16
262 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 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

18 Experts available now in Live!

Get 1:1 Help Now