[RESOLVED] Soonest Date question

[longwolf]

I have a list of businesses with their store hours and days stored as strings.
Example: Store(x).Days = "Wed - Sun"
I'm trying to figure out how to find the soonest date you could visit based on 'Now'

So if today was Friday it would give todays date, but if it were Monday, it would it would give you next wed's date.

[Edgemeal]

I'm not real good with date stuff, but this might work, assuming your store days are represented as 3 chars like, Mon, Tue, Wed, Thu, Fri, Sat, Sun

    Dim StoreDaysTest As String
    Dim TheDay As String, TestDay As String
    Dim n As Integer
    
    StoreDaysTest = "Wed - Sun"
    TestDay = Mid$(StoreDaysTest, 1, 3)  ' get first 3 chars to represent day.
    
    n = 0
    Do
        TheDay = Format(Date + n, "DDD") ' increment day, return first 3 char.
        If TheDay = TestDay Then Exit Do
        n = n + 1
    Loop
    
    Debug.Print Format(Date + n, "long date")

[longwolf]

I'm not real good with date stuff, but this might work, assuming your store days are represented as 3 chars like, Mon, Tue, Wed, Thu, Fri, Sat, Sun

    Dim StoreDaysTest As String
    Dim TheDay As String, TestDay As String
    Dim n As Integer
    
    StoreDaysTest = "Wed - Sun"
    TestDay = Mid$(StoreDaysTest, 1, 3)  ' get first 3 chars to represent day.
    
    n = 0
    Do
        TheDay = Format(Date + n, "DDD") ' increment day, return first 3 char.
        If TheDay = TestDay Then Exit Do
        n = n + 1
    Loop
    
    Debug.Print Format(Date + n, "long date")

Hummm, that should work if 'Date' was Mon-Wed,
But not if date was Thu - Sun
May also need to use DateAdd.

Need sleep now, I'll check it tomorrow ,

Thx

[Edgemeal]

Sorry, guess I misunderstand the Q.

[longwolf]

No Prob, I should have titled this "Soonest date "

[Edgemeal]

EDIT Never mind, see below

[Edgemeal]

OK, I missed a big part of what you are trying to do, my code is just based on your first day, not all the days inbetween!!!!

So I guess you need to get that last day in your storedays too and compare that as well to see the day falls on or before those days.

[akhileshbc]

Below is my code after making some changes...
I tested it and most of the combinations are working... Just try it...

Private Sub Command1_Click()
 Dim StoreDaysTest As String
    Dim TheDay, FDay, LDay As String, TestDay As String
    Dim n As Integer
    
    StoreDaysTest = "Sun - Thu"
    TestDay = Mid$(StoreDaysTest, 1, 3)  ' get first 3 chars to represent day.
    FDay = Mid$(StoreDaysTest, 1, 3)
    LDay = Right(StoreDaysTest, InStr(1, StrReverse(StoreDaysTest), "-") - 2)
    Me.Caption = FDay & "=" & LDay
    n = 0
    Do
        TheDay = Format(Date + n, "DDD") ' increment day, return first 3 char.
        If Checker(TheDay) = Checker(FDay) Or Checker(TheDay) = Checker(LDay) Then 'Exit Do
            MsgBox Format(Date + n, "long date")
            Exit Do
        ElseIf Checker(TheDay) > Checker(FDay) And Checker(TheDay) < Checker(LDay) Then 'Exit Do
            MsgBox Format(Date + n, "long date")
            Exit Do
        'Else
        Else
            n = n + 1
        End If
    Loop
    
    'Debug.Print Format(Date + n, "long date")

End Sub

Private Function Checker(ByVal Temp) As Integer
If Temp = "Sun" Then
    Checker = 1
ElseIf Temp = "Mon" Then
    Checker = 2
ElseIf Temp = "Tue" Then
    Checker = 3
ElseIf Temp = "Wed" Then
    Checker = 4
ElseIf Temp = "Thu" Then
    Checker = 5
ElseIf Temp = "Fri" Then
    Checker = 6
ElseIf Temp = "Sat" Then
    Checker = 7
End If
End Function

-Best of luck
Akhilesh

[Edgemeal]

I took another crack at it. EDIT NM - bad code!

[akhileshbc]

That was nice... Do you find any problems using my code.. edgemeal... I am asking this beacuse I don't know whether there is any bugs in it...

-Akhilesh

[Edgemeal]

Below is my code after making some changes...
I tested it and most of the combinations are working... Just try it...

That doesn't seem right either, cause If you set it to,

StoreDaysTest = "Sat - Fri"

It returns Friday Sept 19th.

EDIT Never Mind, "Sat - Fri" wouldn't be a legit! DUH!!

[Edgemeal]

That was nice... Do you find any problems using my code.. edgemeal... I am asking this beacuse I don't know whether there is any bugs in it...

-Akhilesh

Your solution seems to work OK as far as I can tell. Tho I suck when it comes to working with dates so what do I know!

[akhileshbc]

That doesn't seem right either, cause If you set it to,

StoreDaysTest = "Sat - Fri"

It returns Friday Sept 19th.

Thanks...
My code will check in the order:
Sun,Mon,Tue,Wed,Thu,Fri,Sat

[Edgemeal]

Thanks...
My code will check in the order:
Sun,Mon,Tue,Wed,Thu,Fri,Sat

I doubt anyone would write it as "Sat - Fri", would make more sense as "Fri - Sat", so just disregard all my stupid comments!

[akhileshbc]

[anhn]

Try this:

Function SoonestDate(OpDays As String, Optional ByRef FromDate As Date, _
                                       Optional ByRef ToDate As Date) As Date
   '-- return 0 or 12:00:00 AM if OpDays is invalid
   Const DayUNames As String = "SUN,MON,TUE,WED,THU,FRI,SAT,"
   Dim i1 As Integer, i2 As Integer
   Dim wd1 As VbDayOfWeek, wd2 As VbDayOfWeek
   
   If Not OpDays Like "??? - ???" Then Exit Function
   i1 = InStr(DayUNames, UCase(Left$(OpDays, 3)) & ",")
   If i1 = 0 Then Exit Function
   i2 = InStr(DayUNames, UCase(Right$(OpDays, 3)) & ",")
   If i2 = 0 Then Exit Function
   wd1 = (i1 - 1) \ 4 + 1
   wd2 = (i2 - 1) \ 4 + 1
   If Weekday(Date, wd1) = 1 Then
      FromDate = Date
   Else
      FromDate = Date + 8 - Weekday(Date, wd1)
   End If
   If Weekday(Date, wd2) = 1 Then
      ToDate = Date
   Else
      ToDate = Date + 8 - Weekday(Date, wd2)
   End If
   If FromDate > ToDate Then FromDate = FromDate - 7
   If FromDate > Date Then
      SoonestDate = FromDate
   Else
      SoonestDate = Date
   End If
End Function

Sub GetSoonest()
   Dim FromDate As Date, ToDate As Date, Soonest As Date
   Dim OpDays As String
   
   OpDays = "Tue - Sat"
   Soonest = SoonestDate(OpDays, FromDate, ToDate)
   Debug.Print Format(FromDate, "ddd dd/mm/yyyy") & " - " & Format(ToDate, "ddd dd/mm/yyyy")
   Debug.Print "Soonest: " & Format(Soonest, "ddd dd/mm/yyyy")
   Debug.Print
   
   OpDays = "Sat - Fri"
   Soonest = SoonestDate(OpDays)
   Debug.Print OpDays
   Debug.Print "Soonest: " & Format(Soonest, "ddd dd/mm/yyyy")
   Debug.Print
End Sub

[longwolf]

Thx guys,

anhn,
I liked the way you converted to enums without useing 'Select Case" or a bunch of 'If Then Else's.
I added an Optional StartDate and a PrepOpDays function to make it more powerful.

Public Function SoonestDate(ByVal OpDays As String, _
                            Optional ByRef StartDate As Date, _
                            Optional ByRef FromDate As Date, _
                            Optional ByRef ToDate As Date) As Date
   '-- return 0 or 12:00:00 AM if OpDays is invalid
   'if StartDate is empty, it assumes StartDate is today
    Const DayUNames As String = "SUN,MON,TUE,WED,THU,FRI,SAT,"
    Dim i1 As Integer, i2 As Integer
    Dim wd1 As VbDayOfWeek, wd2 As VbDayOfWeek
    
    If StartDate = 0 Or StartDate = "12:00:00 AM" Then
        StartDate = Date
    End If
    OpDays = PrepOpDays(OpDays)
    If Not OpDays Like "???-???" Then Exit Function
    i1 = InStr(DayUNames, Left$(OpDays, 3) & ",")
    If i1 = 0 Then Exit Function
    i2 = InStr(DayUNames, Right$(OpDays, 3) & ",")
    If i2 = 0 Then Exit Function
    wd1 = (i1 - 1) \ 4 + 1
    wd2 = (i2 - 1) \ 4 + 1
    If Weekday(StartDate, wd1) = 1 Then
        FromDate = StartDate
    Else
        FromDate = StartDate + 8 - Weekday(StartDate, wd1)
    End If
    If Weekday(StartDate, wd2) = 1 Then
        ToDate = StartDate
    Else
        ToDate = StartDate + 8 - Weekday(StartDate, wd2)
    End If
    If FromDate > ToDate Then FromDate = FromDate - 7
    If FromDate > StartDate Then
        SoonestDate = FromDate
    Else
        SoonestDate = StartDate
    End If
End Function

Private Function PrepOpDays(ByVal OpDays As String) As String
'helper function for SoonestDate
'allows more OpDays formats, i.e. "Wed - sun", "monday thru fri" , etc
    Dim sDays() As String
    
    If Len(OpDays) = 0 Then Exit Function
    OpDays = Replace$(UCase(OpDays), " TO ", "-")
    OpDays = Replace$(OpDays, " THRU ", "-")
    OpDays = Replace$(OpDays, " ", vbNullString)
    sDays = Split(OpDays, "-")
    If UBound(sDays) <> 1 Then Exit Function
    If Len(sDays(0)) < 3 Or Len(sDays(1)) < 3 Then Exit Function
    PrepOpDays = Left$(sDays(0), 3) & "-" & Left$(sDays(1), 3)
End Function

[anhn]

If you want to do that then this is a simpler function:

Public Function SoonestDate(ByVal OpDays As String, _
                            Optional ByVal StartDate As Date, _
                            Optional ByRef FromDate As Date, _
                            Optional ByRef ToDate As Date) As Date
   '-- If OpDays is invalid: returns 0 (= #12:00:00 AM# = #12/30/1899#)
   '-- If StartDate is empty or <= 0, it assumes StartDate is today
    Const DayUNames As String = "SUN MON TUE WED THU FRI SAT"
    Dim sDays() As String
    Dim wd(0 To 1) As VbDayOfWeek
    Dim i As Integer, j As Integer
    
    sDays = Split(Replace(UCase(OpDays), " ", "-"), "-")
    If UBound(sDays) = 0 Then Exit Function
    sDays(0) = Left$(sDays(0), 3)
    sDays(1) = Left$(sDays(UBound(sDays)), 3)
    If StartDate <= 0 Then StartDate = Date
    For i = 0 To 1
        If Len(sDays(i)) < 3 Then Exit Function
        j = InStr(DayUNames, sDays(i))
        If j = 0 Then Exit Function
        wd(i) = (j - 1) \ 4 + 1
        wd(i) = Weekday(StartDate, wd(i))
    Next
    If wd(0) = 1 Then FromDate = StartDate Else FromDate = StartDate + 8 - wd(0)
    If wd(1) = 1 Then ToDate = StartDate Else ToDate = StartDate + 8 - wd(1)
    If FromDate > ToDate Then FromDate = FromDate - 7
    If FromDate > StartDate Then SoonestDate = FromDate Else SoonestDate = StartDate
End Function

[longwolf]

My PrepOpDays allows for more formats.
"Wed - Sun", "wed-sun", "Monday to Saterday", "TUE Thru SAT" and more would all all work.

And with your code "Wed - Sun" would become Ubound(sDays) = 3

PrepOpDays could also be used as a public function to format a 'date to date' string before storing it.

BTW, why change the ByRef to a ByVal for StartDate?
It's only going to be changed if it's an un-initialized date variable.

I did some testing and the StartDate test only works if StartDate = "12:00:00 AM".
If you change it to "11:00:00 AM" or "1:00:00 PM" it jumps right past the test.

[anhn]

My PrepOpDays allows for more formats.
"Wed - Sun", "wed-sun", "Monday to Saterday", "TUE Thru SAT" and more would all all work.

And with your code "Wed - Sun" would become Ubound(sDays) = 3

Look closely, you will see that only the first and the last word will be used with

    sDays(0) = Left$(sDays(0), 3)
    sDays(1) = Left$(sDays(UBound(sDays)), 3)

PrepOpDays could also be used as a public function to format a 'date to date' string before storing it.

Up to you if you want like that. I just made it a single self-content function.

BTW, why change the ByRef to a ByVal for StartDate?
It's only going to be changed if it's an un-initialized date variable.

StartDate is passed-in value, no reason to change it then pass back.

I did some testing and the StartDate test only works if StartDate = "12:00:00 AM".
If you change it to "11:00:00 AM" or "1:00:00 PM" it jumps right past the test.

That should be crazy to specify a date that way. The date value of 0 or #12:00:00 AM# is the default value of a Date value (ie. when StartDate is omitted)
To prevent a StartDate comes with non-zero time portion, you can remove the time portion of StartDate with

StartDate = Int(StartDate)
'-- or: StartDate = DateValue(StartDate)
If StartDate <= 0 Then StartDate = Date