Calculate Vacation Days

Posted on 2007-10-11
Last Modified: 2008-01-09
I have a windows form with two datetimepickers.  The first is for when you leave on vacation and the second is when you return from vacation.  I would like to calculate the amount of work days (Mon thru Friday) a person is on vacation including the day they entered as first day of vacation, using the datetimepicker.value.
You take a vacation from Monday October 15 to Monday October 22, you will have been on vacation for five work days, except I want to calculate for larger spans than a week, like months at a time.
Question by:bamaman93081
    LVL 16

    Expert Comment


    Have your dba load this into sql server and call it passing your two date variables

    --===== Change current database to the Master database.
         -- Allows function to be shared by everyone
        USE MASTER
    --===== If the function already exists, drop it
         IF EXISTS (
                    SELECT *
                      FROM dbo.SYSOBJECTS
                     WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
                       AND XType IN (N'FN', N'IF', N'TF')
            DROP FUNCTION [dbo].[fn_WorkDays]
     CREATE FUNCTION dbo.fn_WorkDays
     1.  Given any valid start date and end date, this function will calculate and return
         the number of workdays (Mon - Fri).
     2.  Given only a valid start date (end date has DEFAULT in it), this function will
         return a 1 if the start date is a weekday and a 0 if not a weekday.

     1. MASTER.dbo.fn_WorkDays(@StartDate,@EndDate)
     2. MASTER.dbo.fn_WorkDays(@StartDate,DEFAULT)    --Always returns 1 or 0
     3. MASTER.dbo.fn_WorkDays(@EndDate,@StartDate)
     4. MASTER.dbo.fn_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
     5. MASTER.dbo.fn_WorkDays(@EndDate,@EndDate)     --Always returns 1 or 0

     1.  Holidays are NOT considered.
     2.  Because of the way SQL Server calculates weeks and named days of the week, no
         special consideration for the value of DATEFIRST is given.  In other words, it
         doesn't matter what DATEFIRST is set to for this function.
     3.  If the input dates are in the incorrect order, they will be reversed prior to any
     4.  Only whole days are considered.  Times are NOT used.
     5.  The number of workdays INCLUDES both dates
     6.  Inputs may be literal representations of dates, datetime datatypes, numbers that
         represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
         be implicitly converted to or already is a datetime datatype.
     7.  Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
         transition to a Sunday regardless of the DATEFIRST setting.  In essence, it counts
         only whole weekends in any given date range.
     8.  This UDF does NOT create a tally table or sequence table to operate.  Not only is
         it set based, it is truly "tableless".

     Error Indications:
     1.  If either the @StartDate or the @EndDate parameter is an invalid date, the
         following error is returned...
         "Server: Msg 242, Level 16, State 3, Line 3
          The conversion of a char data type to a datetime data type resulted in an
          out-of-range datetime value."
     2.  If either the @StartDate or the @EndDate parameter is a string not resembling a
         date, the following error is returned...
         "Server: Msg 241, Level 16, State 1, Line 3
          Syntax error converting datetime from character string."
     3.  If only one parameter is passed, the following error is returned...
         "Server: Msg 313, Level 16, State 2, Line 3
          An insufficient number of arguments were supplied for the procedure or
          function MASTER.dbo.fn_WorkDays."

     Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
     Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
     Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
                                        modify to be insensitive to DATEFIRST settings.
    --      Presets
    --===== Define the input parameters (ok if reversed by mistake)
             @StartDate DATETIME,
             @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed

    --===== Define the output data type

    --      Calculate the RETURN of the function
            --===== Declare local variables
            --Temporarily holds @EndDate during date reversal
            DECLARE @Swap DATETIME

            --===== If the Start Date is null, return a NULL and exit
                 IF @StartDate IS NULL
                    RETURN NULL

            --===== If the End Date is null, populate with Start Date value
                 -- so will have two dates (required by DATEDIFF below)
                 IF @EndDate IS NULL
                    SELECT @EndDate = @StartDate

            --===== Strip the time element from both dates (just to be safe) by converting
                 -- to whole days and back to a date.  Usually faster than CONVERT.
                 -- 0 is a date (01/01/1900 00:00:00.000)
             SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
                    @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  ,0)

            --===== If the inputs are in the wrong order, reverse them
                 IF @StartDate > @EndDate
                    SELECT @Swap      = @EndDate,
                           @EndDate   = @StartDate,
                           @StartDate = @Swap

            --===== Calculate and return the number of workdays using the
                 -- input parameters.  This is the meat of the function.
                 -- This is really just one formula with a couple of parts
                 -- that are listed on separate lines for documentation
                 -- purposes.
             RETURN (
                  --Start with total number of days including weekends

                  --Subtact 2 days for each full weekend

                  --If StartDate is a Sunday, Subtract 1
                   -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
                          THEN 1
                          ELSE 0

                  --If EndDate is a Saturday, Subtract 1
                   -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
                          THEN 1
                          ELSE 0
    LVL 96

    Accepted Solution

     Public Shared Function CalculateWorkingDays(ByVal date1 As Date, ByVal date2 As Date) As Integer

        Dim days As Integer = 0

        While Weekday(date1) <> vbMonday And date1 <= date2
          If Weekday(date1) <> vbSaturday And Weekday(date1) <> vbSunday Then
            days += 1
          End If

          date1 = date1.AddDays(1)
        End While

        While Weekday(date2) <> vbMonday And date1 < date2

          If Weekday(date2) <> vbSaturday And Weekday(date2) <> vbSunday Then
            days += 1
          End If

          date2 = date2.AddDays(-1)

        End While

        If Weekday(date1) = vbMonday And Weekday(date2) = vbMonday Then
          days += CInt((date2.Subtract(date1).Days / 7) * 5 + 1)
        End If

        Return days

      End Function

    LVL 27

    Expert Comment

    You know, I think you can just do it like this:

            Dim firstDay As Date = #10/4/2007#
            Dim lastDay As Date = #10/30/2007#

            Dim vacationDays As Int16 = (lastDay.Subtract(firstDay).Days + 1) - _
                        (((lastDay.Subtract(firstDay).Days + 1) / 7) * 2)

            msgbox vacationDays


    Author Comment

    Thanks Bob, that worked like a charm.  I just had to remove the +1 fromt he last calculation because we didn't want to include the day they returned as a vacation day.  Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now