# >40hrs overtime Sunday-Saturdy w/ irregular work-week

1. ## >40hrs overtime Sunday-Saturdy w/ irregular work-week

I download time-sheets showing (among other things) Date, Start & End times. Overtime is paid if >40hrs/week.

Goal:
Create 2 additional columns (Weekly Hrs. & Weekly O/T) to automate straight-time & overtime totals for each work-week
(see yellow hi-lite area w/ manually entered results).

Setup:
- Added column A to show actual weekday names & underlined each end of week section to make it easier to see irregular work-weeks.
- Range-names (Date, Start, & End) limited to each worksheet in "Name Manager" (12 work-sheets per workbook have identical column headings).
- Some entries pay flat-rate (daily rate) not included in O/T calculation (hi-lite in red w/ conditional format, "OR507" column F).
- Downloaded spreadsheet show these 24hr periods of daily rate as "1" in "Hours" column (actually 1 unit not 1 hour) so I created 2 additional columns with formulas to show "Hour Units" & "Day Units" hi-lite in green.

Challenges:
- Weeks are irregular (i.e. work-week can be anywhere form 0-7 days/week)
- Work-weeks carry from one month worksheet to the next month worksheet

Thank you in advance for any thoughts or work done to advance my goal!

2. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

Put all data in 1 table. Use the weeknum function in an extra column. And try to make reports with a pivot table.

3. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

I suppose I could compile all data in a single table if that's easier than creating formula that would work from sheet to sheet, but was hoping there was an easier way.

WEEKNUM function is a good option, I was using WEEKDAY which made it too complicated

Been awhile since I've worked with pivot tables, can you give a small example?

4. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

I feel as if the attached is close to what Vraag en antwoord is suggesting.
The ranges have been combined and converted to a table.
Structured references are used instead of named ranges for the formulas in columns K:L.
Weeknumber is added in column O using: =WEEKNUM(B2,1)
The formula for weekly hours is: =IF(O3<>O2,SUMIFS(K\$2:K2,O\$2:O2,O2),"")
The formula for weekly OT is: =IF(OR(M2="",M2<=40),"",M2-40)
A sample pivot table is shown on the PT sheet.
Let us know if you have any questions.

5. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

Originally Posted by JeteMc
I feel as if the attached is close to what Vraag en antwoord is suggesting.
You have understood that perfectly

6. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

Thank you JeteMc & Vraag for your ideas and efforts!

Looks pretty straight forward now that I see it. The "pivot table" part of Vraag's post was what I stumbled on, I need to go back and study that as I haven't used pivot tables in awhile. I assume this sample file does not use a pivot table - at least not what I was thinking was a pivot table...

You've resolved my original post, now I must apply to the full data to see if there are any holes in my request.

7. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

You're Welcome and thank you for the feedback. If you don't have any other questions then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

8. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

Your approach works, however I'm not sure what is going on in the "PT" worksheet.
- What are all of the little icons with the "-" sign on them in the 1st 7 columns?
- Is that part necessary for the "Jan" worksheet to work or just something you used as a model of my request to work from?

I added reputation for both of you, but not sure if it "took" because the page said something about my "token expired" when I tried to post the quick reply. If it doesn't show up I'll do it again.

9. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

- What are all of the little icons with the "-" sign on them in the 1st 7 columns?
If you want to get rid of the -'s:
On the PivotTable Tools Analyze tab > Options drop down > Options > Display tab (in dialog box) > uncheck Show collapse/expand buttons > OK
Let us know if you have any questions.

10. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

I'm revealing my ignorance here, but I am unfamiliar with what I am looking at so I'm not learning from it.
It's not that I want to get rid of the -'s rather I want to understand what is happening.
Are the 2 worksheets "Jan" & "PT" working together somehow, or are you showing me 2 different ways to accomplish the same thing?
If this is too complex to answer here, I understand, just asking the question.

11. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

I misunderstood.
The -'s are just a way to show that the data is expanded. If it was collapsed then they would be +'s. This probably will not come into play with your workbook, but if there were multiple rows of data for say a date then you could use those -'s and +'s to toggle between an expanded and collapsed display of that data.
Yes, the Jan and PT sheets are working together. The small filter icon in cell A3 (PT sheet) can be used to change the display from January to February or show both at the same time for that matter.
Please feel free to ask any questions for which you feel the need, if I can not answer then hopefully someone will chime in.

12. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

So I played with your sample a bit and it's cool what it can do but I don't really understand how you created it or how it works. I will mark this as solved since someone else might find it immediately useful.

13. ## Re: >40hrs overtime Sunday-Saturdy w/ irregular work-week

Perhaps this video tutorial produced by an instructor at Highline College will do a better job of explaining how a pivot table works than I did.
Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)