Remove time component (decimal) from Date values

[samjsteffes]

Hi all,

I have a table column with date values that are imported from another workbook. The original source of the dates is a database system which time-stamps entries, so each item has a time value as well.

Ultimately, I need to extract a count from this table capturing the number of items that fall within a certain date range. I don't care about the exact date & time, only the date that it occurred on. As such, the time component is messing up the accuracy of this count.

I have searched far and wide, and tried a number of different things to try and get the time portion of the value removed. I know that dates are really numbers (i.e. 43100.55676), so theoretically I should be able to perform manipulations as I would other numbers, like int(value), etc. I also know that just changing the format will not change this underlying serial number. I am certain that the values are indeed dates, and not text, as I am able to change the the format between different date variations as well as the see the underlying serial number.

The below code snippet shows some of the different things I have tried, none of which have worked...

With tbl.ListColumns("Date Reported").DataBodyRange
    .NumberFormat = "General"
    '.NumberFormat = "dd/mm/yy"
    '.Value = DateValue(.Text)
    
    '.Value = .Text
    '.Value = DateValue(.Text)
    
    '.Value = CStr(.Value)
    '.Value = DateValue(.Value)
    
    .NumberFormat = "General"
    '.Value = Int(.Value)
    '.Value = WorksheetFunction.Int(.Value)
    .Value = VBA.Int(.Value)
    .NumberFormat = "dd-mmm-yy"
End With

At this point, I'm at a loss for what the issue is. All the toggling between formats works fine, but none of the commands to manipulate the value ever do anything. I cannot get the time value removed.

Is it a value issue? Do I need to somehow introduce a 'double' type variable and store the values to it since the serial dates are not true integers/long? Or is the the method I've tried to use? Can you not "bulk" change a range of values this way using a 'with' procedure? I'd rather not step through each value individually, as the range is likely to grow quite large over time and a loop would become quite slow.


I'd really appreciate any help/suggestions. All my searching, stealing other solutions, testing my own have resulted in nothing.


Thanks,
sjs

 

[Dryver14]

=left(DateTimecell,5)

gives a text serial but one that works in date formats

 

[samjsteffes]

Hi Dryver, thanks for the answer. Can you be a bit more specific about the correct syntax?

Would I replace the code with:

.value = left(DateTimecell,5)

What exactly is DateTimeCell? It doesn't appear to be a VBA object. The left application requires a string value, and I've not been able to successfully convert the date values to strings either. I tried to do that in a couple of other attempts.

 

[Dryver14]

Obviously datetimecell is something I made up, I'm referring to the cell you are looking at but yes this would give you a 5 digit serial that for instance if yesterday and you had the formula today - left(datetimecell,5) result would be 1

 

[steve the fish]

Does this work? If not id have thought you have text.

With tbl.ListColumns("Date Reported")
    Set x = .DataBodyRange
    x = Evaluate("=IF(ROW(" & x.Row & ":" & x.Rows.Count + x.Row - 1 & "),INT(" & x.Address & "))")
    .DataBodyRange = x
End With

 

[samjsteffes]

Yea, I figured it was a made up variable, but was just checking. I could indeed extract the left 5 digits of the serial number to get the date component, but I need to convert the value to string first, and I've not be able to do that either. I'd also need to convert it back to a value after, as LEFT would return a string.

I'll try something around this approach, but so far I've had no success manipulating the values in any way except to change the NumberFormat.

Thanks for the suggestion.

 

[John_w]

If they are Excel date-times then this should work:

    Dim cell As Range
    For Each cell In tbl.ListColumns("Date Reported").DataBodyRange
        cell.Value = Int(cell.Value)
    Next
    tbl.ListColumns("Date Reported").DataBodyRange.NumberFormat = "dd-mmm-yy"

 

[Dryver14]

=TEXT(LEFT(A1,5),"dd-mmm-yy")

 

[samjsteffes]

Thanks, John_w. This works well. In fairness, I probably should have resorted to using a for loop before now, but I got stubborn and dug my heels in thinking it would be possible without.

Steve the fish's solution works as well, without using a for loop, in case you want to file that away for your own records as well.

Appreciate the feedback.

 

[samjsteffes]

Steve the fish - This works perfectly. Thank you!!

I had tried using a temporary array/variable (i.e. X) in combination with evaluate, but what I came up with failed all the same. I can't recall perfectly as it was a couple days ago, but I believe I tried something like the following:

With tbl.ListColumns("Date Reported").DataBodyRange
    .Value = Evaluate("=INT(" & .Address & ")")
    .NumberFormat = "dd-mmm-yy"
End With

Since evaluate solves an array formula, what it actually did was take the integer of the first value and apply it to the entire range. At that point I knew I wasn't setting up a proper array formula, but I couldn't get it right with any other variations I tried.

Could you explain a bit more what the x.Row & ":" & x.Rows.Count + x.Row - 1 expression does? I can see that this is an IF formula, and that the "value if true" takes the integer of each cell address... but I can't quite interpret what condition the above expression is checking.

In the interest of learning, do you know why something simple like .Value = INT(.Value) does not work? Is it because dates are tricky? Not that I expect everything to be simple; I am curious to know what about VBA syntax prevents such an expression from working. It doesn't result in an error or stop the code.. it just simply has no impact on the values whatsoever (as far as I can tell from stepping through the code).

Thanks again for your help! Really struggled with that one.


- sjs