+ Reply to Thread
Results 1 to 16 of 16

Find Sum of overtime worked between networkdays and holidays in a month

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Find Sum of overtime worked between networkdays and holidays in a month

    HI All,
    I am trying to create a formula to sum overtime worked between a network days in a month and holidays in a month.

    Requesting your support on this. Attached is the format for example.
    Attached Files Attached Files
    Last edited by SHIBUVARGHESE; 05-03-2022 at 04:23 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    How do you imagine you will do this?

    The attached document has a 31 day spread or seemingly so... with Sundays highlighted and a single date reference in the records row 4, A Column. This date represent 1/18/2011. This is the only date reference that you have in the entire document you have shared.

    Networkdays requires a START and END date. To consider HOLIDAYS you need a list of dates that your company, organization, spreadsheet... considers or honors as a holiday. This list of dates would be referenced within the NETWORKDAYS formula...

    But until you give us a realistic spread to evaluate such days this is an impossible task.

    NETWORKDAYS(Start_Date,End_Date,[Holidays])

    Start_Date - As it states, from which date are you checking
    End_Date - As it states, To which date are you checking
    [Holidays] - an array of dates or a reference to a list contained within the sheet


    Good luch
    -If you think you are done, Start over - ELeGault

  3. #3
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Hi ELeGault,

    Thanks for your response,

    I am trying to find total overtime worked in week days (Normal Overtime - NOT) and Holidays (Holiday overtime (All sunday) - HOT).
    I tried by working with the below formula but its not working.

    =SUMIFS($H4:$AL4,$H$2#,NETWORKDAYS.INTL(EOMONTH(DATE($B$1,MONTH($D$1&0),1),0),DATE($B$1,MONTH($D$1&0),1),11))

    Manual Calculation :

    NOT : 50
    SOT : 40

    Its an automated attendance sheet, Once year and month changes, weekdays and Holiday (Sunday only) will change accordingly.

    So discovering a formula according to that.

    Pls refer to the attachment.

    Your reply is highly appreciated.
    Attached Files Attached Files
    Last edited by SHIBUVARGHESE; 05-03-2022 at 02:03 PM.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    try these:

    for NOT HOURS:
    Please Login or Register  to view this content.
    for SOT HOURS:
    Please Login or Register  to view this content.
    with format as number
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  5. #5
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Hi janmorris,

    Got the solution.

    Thank you so much for the formula, I really appreciate your guidance.

    Any chance to make array as dynamic..?

    Array : $H$2:$AL$2 and $H$4:$AL$4

    So it will sum only the overtime mentioned in calendar days. Data entry errors can be avoided.

  6. #6
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    This correction for NOT HOURS will ensure that when there are blank dates, if any hours are underneath then they will not be counted:
    Please Login or Register  to view this content.
    For SOT HOURS, because it is looking for Sunday (7), if the day or date is blank then there is no inclusion of hours under blank cells.

    February 2025 is good example to prove the formulas

    for both formulas, you can change the row 4 range as follows:
    Please Login or Register  to view this content.
    this will allow you to drag down for other rows.
    Last edited by janmorris; 05-03-2022 at 04:05 PM.

  7. #7
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Hi janmorris,

    Many thanks to you.... Formula works perfect

  8. #8
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    What if we want to add more criteria like we want to add public holidays also.

    Attached for reference.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    I have removed the SOLVED tag for now.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    because you are classifying the time as either Normal, Sunday, or Holiday, you need to specify how holiday will be handled.. for example, if holiday is on sunday, then which OT should it be?

  11. #11
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    it should be considered as Holiday Overtime not Sunday Overtime...

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Perhaps the following as applied to the file attached to post #8:
    1. For cell F4: =SUM(I4:AM4)-SUM(G4:H4)
    2. For cell G4: =SUMPRODUCT(($I$2:$AM$2<>"")*(WEEKDAY($I$2:$AM$2,2)=7)*$I$4:$AM$4)-SUMPRODUCT(($I$2:$AM$2<>"")*(WEEKDAY($I$2:$AM$2,2)=7)*(COUNTIF(PUBLICHOLIDAY_LIST,$I$2:$AM$2))*$I$4:$AM$4)
    3. The formula in cell H4 seems to be yielding correct results already.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  13. #13
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Hi JeteMc,

    Thank you so much for the support, the formula works.

    I concluded with the below formula. Attachment for reference.

    NOT
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    SOT
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    HOT
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  15. #15
    Registered User
    Join Date
    05-03-2022
    Location
    DUBAI
    MS-Off Ver
    Microsoft 365
    Posts
    10

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    Thanks for your notes, Have completed both the process.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Find Sum of overtime worked between networkdays and holidays in a month

    RE: Post #13, You're Welcome. 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. Replies: 3
    Last Post: 01-21-2016, 06:07 PM
  2. Replies: 0
    Last Post: 01-18-2016, 03:55 PM
  3. [SOLVED] Add an amount to wages if Overtime worked, but show zero if there is no overtime
    By KazzICC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2014, 01:55 AM
  4. [SOLVED] NETWORKDAYS with a row of Holidays
    By dsepshn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 06:01 AM
  5. Holidays and Networkdays
    By tnoble17 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2010, 10:31 PM
  6. networkdays find holidays
    By cosmo in forum Excel General
    Replies: 1
    Last Post: 06-02-2009, 02:51 PM
  7. Networkdays in VBA including holidays
    By kingdt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2007, 03:14 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