+ Reply to Thread
Results 1 to 3 of 3

Formula to exclude weekends from function but then to "catch-up" function on next day

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    7

    Formula to exclude weekends from function but then to "catch-up" function on next day

    Good Day

    I have a problem where I have a payment amount that needs to be paid over a number of days starting on a particular date. I also need to exclude weekends but then catch up the missing payments over the days following the weekend. i.e. In
    my example I have a payment amount of 1,000 that needs to be paid over 5 days with the first day starting on Friday therefore Saturday and Sunday would be zero but need the formula to catch-up the missing days from Monday onwards.

    I have attached a work as the problem statement if I didn't articulate the problem correctly

    Thanks again
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to exclude weekends from function but then to "catch-up" function on next day

    Please try at
    G7
    =(MEDIAN($C$3,WORKDAY($C$3,$C$2-1),G3)=G3)*NETWORKDAYS(G3,G3)*$C$1/$C$2

    or G8
    =MAX($C$1-SUM($F8:F8),(G3>=$C$3)*NETWORKDAYS(G3,G3)*$C$1/$C$2)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-22-2020
    Location
    Cape Town, South Africa
    MS-Off Ver
    2016
    Posts
    7

    Re: Formula to exclude weekends from function but then to "catch-up" function on next day

    Thank you very much

+ 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. Exclude a Certain Value from the "Average" Function?
    By MLPendleton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2020, 10:45 PM
  2. EXCEL 2003 - Need an "AVERAGEIF" formula to exclude "0" in average
    By kerry0507 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2015, 03:37 PM
  3. modification to the "IF(SUM(COUNTIF" formula or "and" function
    By Tamar81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-10-2013, 12:29 PM
  4. Help Needed to get Filters Using "Include" and "Exclude" multiple values on one column.
    By alfykunable in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2012, 04:03 AM
  5. Replies: 2
    Last Post: 07-11-2007, 06:35 PM
  6. Please add a "sheet" function like "row" and "column" functions
    By Spreadsheet Monkey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2005, 12:15 PM
  7. Replies: 0
    Last Post: 08-24-2005, 04:05 PM

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