+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

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

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    805

    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. #3
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    805

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

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

  6. #6
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

    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. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    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. #8
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

    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. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    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. #10
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

    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. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    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. #12
    Registered User
    Join Date
    03-29-2009
    Location
    Oregon, USA
    MS-Off Ver
    Excel 2016
    Posts
    47

    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. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    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.
    https://www.youtube.com/watch?v=Y2cIAIJAEWA
    Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis
    By HumdrumPanic in forum Excel General
    Replies: 5
    Last Post: 09-30-2020, 12:55 PM
  2. Calculate overtime IF sat or sunday
    By tuts73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2016, 02:34 PM
  3. Replies: 1
    Last Post: 02-09-2016, 09:57 PM
  4. [SOLVED] Calculating Overtime Hours based on 40 hour work week
    By workforceissues in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-11-2015, 05:34 PM
  5. Replies: 6
    Last Post: 05-16-2014, 11:30 AM
  6. Adding Sunday Overtime to hours
    By bluegti02 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2010, 09:44 AM
  7. Replies: 5
    Last Post: 06-24-2010, 06:21 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1