Results 1 to 12 of 12

Calculating Hours Worked on Work Days Per Month

Threaded View

  1. #1
    Registered User
    Join Date
    08-08-2022
    Location
    CT, USA
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Calculating Hours Worked on Work Days Per Month

    Hello, I have been working on an excel sheet to calculate the hours worked on a job per month, evenly distributing the hours across the correct months according to what days work was performed on. Thanks to the help of an excel guru on here I was able to get the formula working without issue. However, I was then instructed that the hours should only be spread across work days (excluding weekends/holidays). I have tried using the NETWORKDAYS() function and I know it is possible, but I still am struggling to get the hours to sum to the correct amount.

    Workdays816.png

    Pictured above is a small recreation of my data with both versions of the formula. On top is the version that works without considering work days(=MAX(0,MIN(G$1,$B3+1)-MAX(F$1,$A3))/$D3*$C3). The bottom calculation is what I have come up with to try and only account for work days (=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(G$8,$B10))/$D10*$C10)). As shown the "Sum" of the hours is off on the latter formula whenever the hours span across 2 or more months. I have tried tweaking the formula many times but I just can't get it to work. The issue is that the formula appears to be adding an extra day to the month before whenever the fist of the month is considered. Any help is appreciated.

    workdaysExampleNew.xlsx
    Last edited by HardlyScene; 08-16-2022 at 08:33 AM. Reason: Updated wording and workbook for clarity

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Hours worked per month in VBA Excel
    By HardlyScene in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-10-2022, 04:11 PM
  2. Calculating Work and worked hours using Pivot
    By rahul_ferns76 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-23-2017, 04:16 PM
  3. Replies: 8
    Last Post: 09-30-2017, 07:00 PM
  4. [SOLVED] How to work out number of 'days' worked based on total hours
    By tangcla in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2016, 10:12 PM
  5. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  6. Replies: 4
    Last Post: 07-15-2010, 11:48 AM
  7. Calculate Work Days Based on Hours Worked
    By FM1 in forum Excel General
    Replies: 7
    Last Post: 02-04-2009, 12:27 PM

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