>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!

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

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?

Originally Posted by JeteMc

I feel as if the attached is close to what Vraag en antwoord is suggesting.

You have understood that perfectly

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.

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.

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.

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.