Printable View
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.
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
Code: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,Quote:
Originally Posted by Edgemeal
But not if date was Thu - Sun
May also need to use DateAdd.
Need sleep now, I'll check it tomorrow ,
Thx
Sorry, guess I misunderstand the Q.
No Prob, I should have titled this "Soonest date "
EDIT Never mind, see below
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.
Below is my code after making some changes...:)
I tested it and most of the combinations are working... Just try it...:)
-Best of luck:thumb:Code: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
Akhilesh
I took another crack at it. EDIT NM - bad code!
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
That doesn't seem right either, cause If you set it to,Quote:
Originally Posted by akhileshbc
StoreDaysTest = "Sat - Fri"
It returns Friday Sept 19th.
EDIT Never Mind, "Sat - Fri" wouldn't be a legit! DUH!!
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! :o :lol:Quote:
Originally Posted by akhileshbc
Thanks...:)Quote:
That doesn't seem right either, cause If you set it to,
StoreDaysTest = "Sat - Fri"
It returns Friday Sept 19th.
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!Quote:
Originally Posted by akhileshbc
:):wave:
Try this:
Code: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
Code: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
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.
Code: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
If you want to do that then this is a simpler function:
Code: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
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.
Look closely, you will see that only the first and the last word will be used withQuote:
Originally Posted by longwolf
Code:sDays(0) = Left$(sDays(0), 3)
sDays(1) = Left$(sDays(UBound(sDays)), 3)
Up to you if you want like that. I just made it a single self-content function.Quote:
Originally Posted by longwolf
StartDate is passed-in value, no reason to change it then pass back.Quote:
Originally Posted by longwolf
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)Quote:
Originally Posted by longwolf
To prevent a StartDate comes with non-zero time portion, you can remove the time portion of StartDate with
Code:StartDate = Int(StartDate)
'-- or: StartDate = DateValue(StartDate)
If StartDate <= 0 Then StartDate = Date