Convert Date/Time cell to a date and time in VBA

[Aurillius]

Hi,
I've got a column of cells with date/time values and I need to compare to an specific date and time in VBA. I'm using a CASE where cell.value appears to be stored as decimal. How do I format specific the date and time that I created to see if the cell date/time is before or after?

Cell D2 = 2023-01-10 6:21:27 AM ...this is first cell in the column
shiftdate = 2021-01-10 ...and is a date variable

Here the statement:

Select Case Workbooks("text.xlsm").Worksheets("Sheet").Range("D2").Value 'debugger shows this as 44936.2648958333

Case Is < shiftdate & " " & "4:58:00 AM" ' it doesn't matter how early or later I make this time, it always goes into the case.

...

Thanks,
Mark

 

[dhookom]

This forum is for MS Access database coding. You might have better luck in VBA Visual Basic for Applications.

Also, do you realize your shift date is in the year 2021 and the cell is 2023? Dates are all actually stored as numbers of days since 12/30/1899 (I think).

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Yes, you need to ask your question in forum707, but consider comparing 'apples to apples':

Option Explicit

Sub test()
Dim shiftdate As [blue]Date[/blue]

shiftdate = [blue]CDate([/blue]"2021-01-10"[blue])[/blue]

Select Case [blue]CDate([/blue]Workbooks("text.xlsm").Worksheets("Sheet").Range("D2").Value[blue])[/blue]
    Case Is < [blue]CDate([/blue]shiftdate & " " & "4:58:00 AM"[blue])[/blue]
        Debug.Print "We are here"
    Case Else
        Debug.Print "This is else"
End Select

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson