+ Reply to Thread
Results 1 to 10 of 10

How to omit holidays and Sunday in DateDiff function

  1. #1
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    721

    How to omit holidays and Sunday in DateDiff function

    Hi,

    I am extracting count of days in date difference

    we have 15 holidays List and each month sundays where we haven't work.

    i want to omit that days while taking count of two date.

    my code is as below:

    iCount = DateDiff("d", rsStatus.Fields(2).Value, rsStatus.Fields(6).Value)

    Please suggest
    Last edited by mohan.r1980; 12-23-2017 at 01:55 AM.

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    Try this code:

    Please Login or Register  to view this content.
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    721

    Re: How to omit holidays and Sunday in DateDiff function

    Hi than_gold,

    thanks for reply.

    is it consider holiday list?

    is it possible to datediff or any other function?
    Last edited by mohan.r1980; 12-21-2017 at 09:00 AM.

  4. #4
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    it is consider the holiday.
    let me hunt of using dated iff or other existing function in excel.

  5. #5
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    use Networkdays
    check this link:
    https://support.office.com/en-us/art...e-5005e3eb18e7
    using this Neworkdays, you can define the holidays.

  6. #6
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    mohan.r1980
    Did you check that Networkdays?

  7. #7
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    721

    Re: How to omit holidays and Sunday in DateDiff function

    Hi than_gold,

    Sorry for late reply.

    i can't see the suggested site because of limitation of internet in our office.

    could you please post here of that script.

  8. #8
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    sure, here you go :

    Syntax
    NETWORKDAYS(start_date, end_date, [holidays])

    The NETWORKDAYS function syntax has the following arguments:

    Start_date Required. A date that represents the start date.

    End_date Required. A date that represents the end date.

    Holidays Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

    Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.networkdays.PNG
    Last edited by than_gold; 12-22-2017 at 02:38 AM. Reason: screenshot of the function

  9. #9
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: How to omit holidays and Sunday in DateDiff function

    Is that what you want??

  10. #10
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    721

    Re: How to omit holidays and Sunday in DateDiff function

    hi than_gold,

    thanks for solution,
    its working fine.

+ 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. Calculation of TAT between two dates,excluding holidays, sat and sunday.
    By pravincmore26 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2017, 07:40 AM
  2. [SOLVED] Time sheet calculations for Sunday work and public holidays.
    By Albert Dirk in forum Excel General
    Replies: 2
    Last Post: 04-14-2016, 04:55 AM
  3. [SOLVED] Count 6 working days (excluding Sunday, Holidays & half days)
    By eve_star1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 08:00 AM
  4. Replies: 6
    Last Post: 11-16-2013, 04:29 PM
  5. Excel DateDiff Function
    By janagan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2013, 04:14 AM
  6. Counting Weekdays + Saturday, omit holidays
    By bryceowen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-20-2008, 11:29 AM
  7. Where is DateDiff function in Excel 2002 ?
    By Nigel Welch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2005, 12: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