Extracting Integer from DateTime values

[samjsteffes]

Hi all,

Hopefully its just a quick solution needed here:

I have a table column that contains imported datetime values. I have formatted them as 'dd-mmm-yy', and am trying to extract counts that fall between different date ranges. However, I've noticed that the values are not truly integers, but instead carry a decimal value (i.e. 43109.44722). This is messing up the counts that I am extracting, as the date ranges I choose will be for example: 43100.0000 to 43109.0000.

After some research I've tried the following to no avail:

With tbl.ListColumns("Date Reported").DataBodyRange
    .NumberFormat = "General"
    .Value = Int(.Value)
    .NumberFormat = "dd-mmm-yy"
End With

I've stepped through the code, and the .NumberFormat commands are performing as expected, but the Int(.Value) has no effect on the values; they maintain their decimal throughout the code before converting back to 'dd-mmm-yy' format.

I've also tried the CInt function (which is not ideal, since I wan't everything rounded down), as well as a more robust WorksheetFunction.RoundDown(.Value, 5).

What am I overlooking?


Appreciate any help!

-sjs

 

[jim may]

Try modifying your code-line:

.Value = Int(.Value)​

as

.Value = WorksheetFunction.Int(.Value)​

 

[samjsteffes]

Try modifying your code-line:

.Value = Int(.Value)​

as

.Value = WorksheetFunction.Int(.Value)​

Thanks, Jim. I've tried this, but unfortunately, the INT function is not available in the WorksheetFunction object class. Also, I had a similar issue with the WorksheetFunction.RoundDown(), where it had no effect on the values.

I've since also tried:

.value = Evaluate("=INT(" & .Address & ")") - but as this is more appropriate for array formulas, it evaluates the same result to the entire range.

.formula = "=INT(" & .Address & ")"
.value = .value

The last was an attempt to temporarily make each cell a formula, execute the integer function and then convert back to just the value.

Seems I'll have to keep looking. Appreciate the feedback, nonetheless!

-sjs

 

[samjsteffes]

Sorry, Jim. It appears as though I have misspoke. Even though the INT function does not appear to be an acceptable option of the WorksheetFunction object class (as in its not one of the options in the popup menu when you enter WorksheetFunction.) you can indeed enter the code you have suggest. All the same, it has no effect on the values, same as with the WorksheetFunction.RoundDown() method I tried.

Sorry for my ignorance. Thanks again for the suggestion.