Solved

DateDiff without Sunday

Posted on 1998-06-29
7
531 Views
Last Modified: 2008-03-17
How can I compute the number of days between two Dates so that there is no Sunday in the result.
0
Comment
Question by:kea
7 Comments
 

Author Comment

by:kea
ID: 1975942
-
0
 
LVL 18

Expert Comment

by:deighton
ID: 1975943
Here is a code fragment that i hope will work for you

    Dim X, y
    Dim Z As Long

    X = CVDate("01-01-1998")   'YOUR DATES ARE HERE   (First Lowest Date)
    y = CVDate("09-01-1998")   ' Higher (later Date)

    Do Until X = y

        X = X + 1
        If Weekday(X, 1) <> 1 Then
            Z = Z + 1
        End If

    Loop

    MsgBox "NO OF DAYS EX SUNDAY " + Str(Z)
0
 
LVL 4

Accepted Solution

by:
tomook earned 50 total points
ID: 1975944
' I left some debuggers in but commented out in case
' you want to pick the routine apart.
Function DateDiffWithoutSundays(LowDate As Date, HighDate As Date)
    ' Figure out the number of days between two dates
    ' not counting Sunday.
   
    Dim DayOfLowDate As Long, DayOfHighDate As Long
    Dim DaysBetween As Long
    Dim DayOfWeekLow As Integer, DayOfWeekHigh As Integer
    Dim SundaysPast As Long
    'Dim sTmp1 As String
   
    ' Figure out the raw number of days between.
    ' Note that these are assumed to be "even" days,
    ' i.e. no time of day included.
    DaysBetween = CLng(HighDate - LowDate)
   
    'sTmp1 = "Date 1: " & Format(LowDate, "mm/dd/yyyy") & vbCrLf
    'sTmp1 = sTmp1 & "Date 2: " & Format(HighDate, "mm/dd/yyyy") & vbCrLf
    'sTmp1 = sTmp1 & "Days Difference = " & DaysBetween
    'Debug.Print sTmp1
   
    ' DatePart("w", ..., vbSunday) returns the day of the week
    ' with Sunday = 1 and Saturday = 7
    DayOfWeekLow = DatePart("w", LowDate, vbSunday)
    DayOfWeekHigh = DatePart("w", HighDate, vbSunday)
   
    'sTmp1 = Format(LowDate, "mm/dd/yyyy") & " is day " & DayOfWeekLow & vbCrLf
    'sTmp1 = sTmp1 & Format(HighDate, "mm/dd/yyyy") & " is day " & DayOfWeekHigh
    'Debug.Print sTmp1
   
    ' Figure out how many whole weeks passed by using
    ' an integer divide. We could also use DateDiff,
    ' but we have no compelling reason in this case.
    SundaysPast = DaysBetween \ 7
   
    ' If a partial week passed, and it crossed Sunday, add another week
    ' to WeeksPast. We know a partial week passed because:
    ' 1. DayOfWeekLow <> DayOfWeekHigh AND
    ' 2. DayOfWeekLow > DayOfWeekHigh
    ' Looking at the logic, we need only check for the second case.
    If DayOfWeekLow > DayOfWeekHigh Then SundaysPast = SundaysPast + 1
   
    'sTmp1 = SundaysPast & " sundays went by"
    'Debug.Print sTmp1
   
    ' Now it is easy.
    DaysBetween = DaysBetween - SundaysPast
       
    'sTmp1 = DaysBetween & " days between, not counting Sundays"
    'Debug.Print sTmp1

    DateDiffWithoutSundays = DaysBetween
End Function

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 17

Expert Comment

by:ramrom
ID: 1975945
Why did you reject deighton's answer? We who invest time in answering questions are helped by some feedback. My first hit was "efficiency", yet on my 686x166 w/65 MB RAM it processed 180,000 days in 3 seconds. tomook's answer, of course is optimal, since it avoids the loop.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1975946
Well, deighton's answer runs in O(n) time, where n is the days between the dates. The one I posted runs in constant time. Almost always better, my CSci prof used to say.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1975947
Well, deighton's answer runs in O(n) time, where n is the days between the dates. The one I posted runs in constant time. Almost always better, my CSci prof used to say.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1975948
btw, I do agree with your real point that feedback on why an answer is rejected is always appreciated.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

861 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

23 Experts available now in Live!

Get 1:1 Help Now