working with date and time

[ccordner]

I have a list of events in a sheet, each with a timestamp (using the "Now" function in vba)

On a userform, I have a specified start date and time and end date and time.

Is there any way of combining a date and time into one variable?

I want to search for all events between two times (which will by necessity be on two separate days) without having to test the dates and times separately?

 

[VoG]

Try like this

DandT = DateValue(TextBox1.Text) + TimeValue(TextBox2.Text)

 

[ccordner]

Tried that, but for some reason the start-time works alright, but the endtime shows up a type mismatch.

The values have transferred from the textboxes correctly, so I'm not sure why!

Dim Address, DistributionList As String
Dim LogCount, ReportCount As Long
Dim EndOfLog As Boolean
Dim StartTime, EndTime As Date

Private Sub cmdok_click()
LogCount = 2
ReportCount = 5
EndOfLog = False
While EndOfLog = False
    If Sheets("Log").Cells(LogCount, 1) = "" Then
        EndOfLog = True
        Else
        StartTime = txtDateFrom.Value + txtTimeFrom.Value
        EndTime = txtDateTo.Value + txtTimeTo.Value
        If StartTime <= Sheets("Log").Cells(LogCount, 2) And EndTime <= Sheets("Log").Cells(LogCount, 2) Then
            Sheets("Template Log").Cells(ReportCount, 1) = Sheets("Log").Cells(LogCount, 1)
            ReportCount = ReportCount + 1
            LogCount = LogCount + 1
            Else
            LogCount = LogCount + 1
        End If
    End If
Wend
    
If ReportCount = 5 Then
    Sheets("Template Log").Cells(5, 1) = "No Events to record"
End If
    
Unload Me
End Sub

 

[VoG]

Try

Dim Address As String, DistributionList As String
Dim LogCount As Long, ReportCount As Long
Dim EndOfLog As Boolean
Dim StartTime As Date, EndTime As Date

Private Sub cmdok_click()
LogCount = 2
ReportCount = 5
EndOfLog = False
While EndOfLog = False
    If Sheets("Log").Cells(LogCount, 1) = "" Then
        EndOfLog = True
        Else
        StartTime = DateValue(txtDateFrom.Value) + TimeValue(txtTimeFrom.Value)
        EndTime = DateValue(txtDateTo.Value) + TimeValue(txtTimeTo.Value)
        If StartTime <= Sheets("Log").Cells(LogCount, 2) And EndTime <= Sheets("Log").Cells(LogCount, 2) Then
            Sheets("Template Log").Cells(ReportCount, 1) = Sheets("Log").Cells(LogCount, 1)
            ReportCount = ReportCount + 1
            LogCount = LogCount + 1
            Else
            LogCount = LogCount + 1
        End If
    End If
Wend
    
If ReportCount = 5 Then
    Sheets("Template Log").Cells(5, 1) = "No Events to record"
End If
    
Unload Me
End Sub

 

[ccordner]

Thanks, that works great, but for some reason it isn't picking up the right dates from the list.

I'll have a play.

Thanks
Chris.

 

[ccordner]

Just put some msgboxes into that.

For some reason, it is correctly filtering events after the start time, but not those before the end time, unless they are the same day.

It is merging the dates/times into one variable correctly, so essentially, it is saying that "13/03/11 17:26" is not less than "14/03/11 04:00".

I could work around this by testing the date separately, but was hoping to avoid that.

Any ideas?

Chris

 

[ccordner]

Still not having much luck with this one!

I have the following dates and times in the range B2:B6:

20/04/2011 03:59; 20/04/2011 04:00; 20/04/2011 17:26; 21/04/2011 03:59 and 21/04/2011 04:00.

Ultimately, this will contain a much longer list of date/time stamps for events which happen during the day. However, I want to be able to extract all the events between 04:00 one day and 03:59 the following day.

On the userform, I have txtTimeFrom, txtDateFrom, txtTimeTo and txtDateTo textboxes, containing "04:00", "4/20/2011", "03:59" and "4/21/2011" respectively.

The code snippet used to test the events is this:

LogCount = 2
ReportCount = 5
EndOfLog = False
While EndOfLog = False
    If Sheets("Log").Cells(LogCount, 1) = "" Then
        EndOfLog = True
        Else
        StartTime = DateValue(txtDateFrom.Value) + TimeValue(txtTimeFrom.Value)
        EndTime = DateValue(txtDateTo.Value) + TimeValue(txtTimeTo.Value)
        If StartTime <= Sheets("Log").Cells(LogCount, 2) Then AfterStart = True Else AfterStart = False
        If EndTime <= Sheets("Log").Cells(LogCount, 2) Then BeforeEnd = True Else BeforeEnd = False
        
        MsgBox (Sheets("Log").Cells(LogCount, 7) & Chr(10) & "Start date/time: " & StartTime & Chr(10) & "Event date/time: " & Sheets("Log").Cells(LogCount, 2) & Chr(10) & "End date/time: " & EndTime & Chr(10) & "After Start: " & AfterStart & Chr(10) & "Before End: " & BeforeEnd)

Yet, when I run this, it gives some curious results such as:

"Event 3; Start date/time: 20/04/2011 04:00:00; Event date/time: 20/04/2011 17:26:33; End date/time: 21/04/2011; AfterStart: True; BeforeEnd: False"

"Event 5; Start date/time: 20/04/2011 04:00:00; Event date/time: 21/04/2011 04:00:00; End date/time: 21/04/2011; AfterStart: True; BeforeEnd: True"

Any ideas?

Thanks
Chris

 

[xenou]

Are the dates on the worksheet real dates or text?

It may be easier to do this on the worksheet. Enter the times in input squares and then use the formula in column D to get your "hits":

<img alt="excel worksheet" src="http://northernocean.net/etc/mrexcel/20110421_dates.png">

Formula in Cell C5 is:
=TIMEVALUE(RIGHT(B5,5))

Formula in Cell D5 is:
=AND(C5>=$D$2,C5<=$D$3)

(These assume you have text dates, not real dates).

BTW, your post is unclear because you originally said you want to test for event between two times no matter what date, but your last post is testing based on dates and times, not just times. If you just want to test for times, why is it that you want "dates and times together in one variable" as stated in your first post? It would seem that you would rather have time in one variable, without a date ...