Need to total hours for 3 seperate time ranges by location

[collinjones]

I have a large file I will need to update daily with daily records of hours worked. Each row contains Job Code, Locaiton as well as date and hours worked.

I need a formula or set of formulas to give me three values for a certain group of information. For example I have all this information, it needs to be sorted by location and job code (done). I then need the total hours worked under each job code for each location for the month, week, and yesterday. Please help!!

How can i get there using complex formulas....i wish i knew SQL or VBA

 

[SkipVought]

Hi,

Sounds like you ought to use the PivotTable Wizard. It is a GREAT summary reporting tool.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[collinjones]

how would I get the month to date, week to date, and yesterday's hours totals?

 

[SkipVought]

Right click the date field (ROW or COLUMN) in the PivotTable, and select Group and Outline. In the Group Box...

for Month, select Month & Year (if you do not select year it will group ALL like months' data together for ALL years)

for Week, select Day and make the day count 7. You may need to adjust the STARTING date in order to get the correct week.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[AnotherHiggins]

My first preference would be Pivot Tables.

If you have trouble with the *-To-Date groupings, I'd probably suggest using a summary sheet and leave the data available with AutoFilters turned on if anyone wants to find details rather than having summaries under each section.

But if you want it organized that way - summaries under each section - then sort (as you already have), then go to Data > SubTotals. This will produce collapsible divisions based on job code.

You can then add formulas to count *-To-Date data. For either of the second two methods, you could use the following formulas:

Month-to-Date:
[COLOR=blue white]=SUMPRODUCT(--(Jobcode=A1) * --(Date>TODAY()-DAY(TODAY())) * --(HoursWorked))[/color]

Week-to-Date:
[COLOR=blue white]=SUMPRODUCT(--(Jobcode=A1) * --(Date>=INT((TODAY()-2)/7)*7+2) * --(HoursWorked))[/color]

Yesterday:
[COLOR=blue white]=SUMPRODUCT(--(Jobcode=A1) * --(Date=TODAY()-1) * --(HoursWorked))[/color]

where Jobcode, Date and HoursWorked are named ranges and A1 contains the desired JobCode.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[collinjones]

Ok so the Pivot Table is working good, giving me the data I need and the grouping seems to be working fine.

Now..I have a certain format i need to present this dats. I am working through linking the spreadsheet and am thinking that the only link that will cause me problems is the links for 'yesterdays' totals.

here is the current formula in my report...
=GETPIVOTDATA("Total Hours (Sum)",'Pivot Table'!$A$3,"Business Date",DATE(2007,10,10),"Job Code Name","Bar back","Location Name","BWI Airport","Business Date2","week2")

Can I replace DATE(2007,10,10) with something to give yesterdays date so the link will not need to be udated everyday?

Since the rest of the data i will need is a sum of grouped data the link forula should update itself, right?

 

[SkipVought]

[tt]
=today()-1
[/tt]


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[collinjones]

DUH....geez i feel smart

I knew that, i was figuring there was some more complicated term

 

[lefty78312]

The subject of this forum hits close to a question I've been pondering. In fact, I found this forum on a google search.

At work, I created a single-page spreadsheet with a sumproduct formula to count data that meets certain criteria and how many sales result from the data that meets the criteria. I also notate the time of each data occurrence. Now I just need to break the relevant data into time periods, arbitrarily every 2 hours, with the ability to change it to other time periods; say, 4 hours, if need be.

In other words, I want to know be able to track how many occurrences of each type of event occur within a certain time period, e.g., 8:00-10:00, 10:01-12:00, 12:01-2:00, etc., and how many sales occur during that same period. I can then easily track my closing percentage by time period. BTW, since I do the entire month on one spreadsheet, the times are not in chronological order.

I tried a Pivot Table, but I don't understand it well-enough to do it properly. I'm sure a modified sumproduct formula would do the trick; I just don't know how to formulate the time periods so Excel will know what I'm asking. All I end up with is error messages.

Thank you in advance.

 

[SkipVought]

lefty78312,

Please post your question in a new thread.

And welcome to Tek-Tips.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}