VBA Dates/Times - getting midnight???

[combatvolcano]

I have tried several ways of doing this...

I have a cell on the "settings" sheet which is a date time formatted as the *standard mm/dd/yyyy

I have tried either dim'ing my variable dd as date or not and assign it using

dd = sheets("settings").Range("b2").Value

I am trying to compare this to another date/time

When I use a msgbox to look at the other date/time I see a valid date/time in the msgbox (even when using DateValue() on it)

If I do the same thing to "dd" I get " 12:00:00 AM"

It seems this is actually the value because if I try DateDiff I get a huge -number????

Even this doesn't seem to work!

    ddy = Year(Sheets("SETTINGS").Range("B2").Value)
    ddm = Month(Sheets("SETTINGS").Range("B2").Value)
    ddd = Day(Sheets("SETTINGS").Range("B2").Value)
    dd = DateSerial(ddy, ddm, ddd)
    MsgBox (dd)

I am confused, I just want to compare two date/times in VBA to if one is greater! (and delete the row if it is lesser but I have a handle on that part)

thank you!

 

[combatvolcano]

I should note that to get the B2 value I am using MAX() on a column from another sheet - could this be the problem??? It seems to be a valid date/time??

 

[TKB]

Not knowing what your entire code looks like. If both boxes are formatted as dates, then use an IF-Then statement.

IF Sheets("SETTINGS").Range("B2").Value > (cell being compared to) Then

Macro

Else

(Select next cell you want to check via a set location, or ActiveCell.Offset(Row offset , Column Offset))

End If

I havent had a chance to try it myself, i in theory that should work.

 

[combatvolcano]

Thanks for the reply - I fully understand how to iterate through the column and do whatever other functions are necessary, the problem is that the code:

Dim dd as date
dd = sheets("settings").range("b2").Value

Gives me only 12:00:00 when B2 does indeed have a valid date/time in it.

Even reconstructing the date/time from this value doesn't seem to work as shown in the first post.

Why am I getting 12:00:00 as the datetime??? with no date/month/year in it???

 

[TKB]

does the dd cell have both a date and a time in it?

 

[combatvolcano]

It is a cell formatted as a date/time value
All other functions for date/time values seem to work just fine

It displays in the cell as 6/10/2012
The numeric value is 41070.00

Maybe try running this code? Just format a cell as "date" and type in 6/10/2012

Dim dd As Date
    ddy = Year(Sheets("SETTINGS").Range("c2").Value)
    ddm = Month(Sheets("SETTINGS").Range("c2").Value)
    ddd = Day(Sheets("SETTINGS").Range("c2").Value)
    dd = DateSerial(ddy, ddm, ddd)
    MsgBox (dd)

I get "12:00:00 AM" and I can't understand why. I can go Day(dd) and get a day returned! When I try to do the comparison it doesn't work and I just don't know what I am missing.

 

[combatvolcano]

Here is one way I've tried the comparison.

    While ActiveCell.Value <> ""
    MsgBox (DateDiff("s", ActiveCell.Value, dd))
        If ActiveCell.Value < dd Then
            Selection.EntireRow.Delete
        End If
        ActiveCell.Offset(1, 0).Range("A1").Select
    Wend

 

[TKB]

Select "Settings" "C2" and do this:

 Selection.NumberFormat = "m/d/yyyy"

and then make sure that the cell its being compared to is also formatted like this

Then the comparison should work with a > or < depending on how you order the formula.

 

[combatvolcano]

Doesn't change anything... that was already the format that I was using.

I guess I will have to insert a column with a formula that does it and look at the formula value. I hate VBA so much.

 

[TKB]

I think when you set the dd = something, you might be making it a bit more complicated than you have to.

Just use;

If (Cell with first variable).Value > (Cell with second variable).Value Then

Run Macro

Else (otherwise)

Do this

End If