Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

DateDiff without Sunday

How can I compute the number of days between two Dates so that there is no Sunday in the result.
0
kea
Asked:
kea
1 Solution
 
keaAuthor Commented:
-
0
 
deightonCommented:
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
 
tomookCommented:
' 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
ramromconsultant Commented:
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
 
tomookCommented:
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
 
tomookCommented:
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
 
tomookCommented:
btw, I do agree with your real point that feedback on why an answer is rejected is always appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now