+ Reply to Thread
Results 1 to 2 of 2

Networkdays with holiday - start date falls on a holiday

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    5

    Networkdays with holiday - start date falls on a holiday

    Data: Start date and time in col F, end date and time in col L, lead time (hh:mm:ss) in col M. Lead time calculation formula:=IF(ISBLANK(L2),(NETWORKDAYS(F2,NOW(),Holidays)-1+MOD(NOW(),1)-MOD(F2,1)),(NETWORKDAYS(F2,L2,Holidays)-1+MOD(L2,1)-MOD(F2,1)))
    Lead time is calculated as soon as the start date is entered, considering current time (NOW) as end date until actual end date and time are entered.
    2nd October is in holiday list.

    Issue: For rows with start date as 2nd october, lead time does not give the correct result.
    E.g. start date and time = 02-10-2014 13:56:00 and End date and time = 07-10-2014 12:03.
    Result should be 60:03:00 but I get 46:07:00

    I guess this is because, though 2nd october is considered holiday, start time is calculated from 13:56 and not as 3rd october 00:00

    Hope I have clearly laid the case. Any help appreciated.

  2. #2
    Registered User
    Join Date
    10-08-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    5

    Re: Networkdays with holiday - start date falls on a holiday

    =NETWORKDAYS(F2,IF(L2="",NOW(),L2),Holidays)+NETWORKDAYS(IF(L2="",NOW(),L2),IF(L2="",NOW(),L2),Holidays)*(MOD(IF(L2="",NOW(),L2),1)-1)-NETWORKDAYS(F2,F2,Holidays)*MOD(F2,1)

    this one worked.

+ 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. NETWORKDAYS IF date falls on a holiday, otherwise...
    By Thunderer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2014, 02:47 PM
  2. Find if holiday falls within date range
    By icyrius in forum Excel General
    Replies: 18
    Last Post: 01-03-2013, 12:12 PM
  3. how to sum day if it falls in a weekend or holiday?
    By jgomez in forum Access Tables & Databases
    Replies: 2
    Last Post: 11-30-2011, 01:54 PM
  4. Excel 2007 : Day a holiday falls
    By mcinnes01 in forum Excel General
    Replies: 17
    Last Post: 06-11-2010, 11:10 AM
  5. Using NETWORKDAYS to find holiday date clashes
    By Zyphon in forum Excel General
    Replies: 6
    Last Post: 02-01-2008, 04:31 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