Results 1 to 6 of 6

network days formula not handling cases when start date is weekend

Threaded View

  1. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: network days formula not handling cases when start date is weekend

    I started the formula from the scratch and ended with such construction:
    Formula: copy to clipboard
    =MAX(0,MIN(FLOOR.MATH(A2)+TIME(16,0,0),B2)-WORKDAY(A2-1,1,$R$2:$R$100)-MAX(A2-FLOOR.MATH(A2),TIME(8,0,0)))+
    MAX(0,NETWORKDAYS(WORKDAY(A2,1,$R$2:$R$100),WORKDAY(B2,-1,$R$2:$R$100),$R$2:$R$100))*TIME(8,0,0)+
    IF(FLOOR.MATH(A2)=FLOOR.MATH(B2),0,MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0)))

    it consists of three rows (three parts):
    - first is for time in starting day (is 0 if it's non-working day or time after 16:00)
    - second is the number of working days in between day after first day and a day before last day, multiplied at the end by 8 hours per day:
    two subcomments here:
    a) *TIME(8,0,0) it is equivalent to end hour minus start hour: *(TIME(16,0,0)-TIME(8,0,0)) - worth noticing if starting and ending hours are written in some cells).
    b) situation of no such days is handled by using MAX(0,dates difference)
    - third is the number of hours on last day, but only if last day is different than first day - IF used. It could be also written as (FLOOR.MATH(A2)<>FLOOR.MATH(B2))*(MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0))) (I originally wrote it that way, but IF is probably easier readable).

    Just to analyze (and correct? if needed) I left columns F/N(P) with partial results I used while creating the formula. To show they are not needed any longer I removed them in rows 4:9
    Delete all them from final version.
    Attached Files Attached Files
    Best Regards,

    Kaper

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Start Date + 8 Days but Can't End on a Weekend/Holiday
    By rebma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-21-2021, 11:11 AM
  2. [SOLVED] Formula to count how many AM and PM sessions within a date range using Network Days
    By hodge1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-11-2016, 10:03 AM
  3. Replies: 24
    Last Post: 10-08-2014, 10:31 AM
  4. [SOLVED] Find date using start date and number of network days
    By v!ctor in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 05:27 PM
  5. Replies: 5
    Last Post: 12-15-2011, 11:16 AM
  6. aging formula with network days from start to today
    By eddienole in forum Excel General
    Replies: 2
    Last Post: 08-30-2011, 10:43 AM
  7. Excel running date formula minus weekend days
    By Eliane in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2005, 06:06 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