Removing time from date for an entire data stored in an array

Question

Using Excel VBA, how would I apply the formula Date(year(A1),Month(A1),Day(A1)) to all the dates in an entire column? Currently the data is in date time format (e.g. 30/04/1995 9:35:00 AM) but I want just the date - 30/04/1995.

Currently all of the dates are stored in an array and I have tried Columns(data_column).NumberFormat = "[$-1009]d-mmm-yy;@", but I have not been successful in removing the time from the dates.

Solution 1

If you want to change the display format of your data while preserving the time information, you can use something like NumberFormat = "mm/dd/yyyy" in cells of a worksheet or chart axis.

Excel stores date/time info as a floating point number. The days are to the left of the decimal and fractions of days (hh:mm:ss) are to the right. If you want to strip out the time information from the underlying data, then convert to Long then back to Date. In VBA (thanks to T.M. for the correction):

DateOnly = CDate(Int(DateTime))

In worksheet formulas just convert to Int and format as you please

DateOnly = INT(DateTime)

Hope that helps

Solution 2

All dates in Excel include time: datetime. It is not possible to store a date only or a time only in a cell. The reason is that Excel stores them as numbers. Anything before the decimal point is the date and anything after the decimal point is the time. So, even if you put the number 42000 in a cell (without anything after the decimal point) and change the format of that cell to date, the value will still be December 27, 2014 (42000 days after December 31, 1899) with an assumed time of zero = 00:00:00 in the morning.

Since all numbers can potentially have something after the decimal point, all dates have time in Excel and all times have dates.

The only thing you can do is: format a cell to show only the date part or the time part or both. So, all you need to do is to hide the time.

If you want to change all dates to have zero after the decimal point then you'll have to loop through the numbers and change all values to INT values.

For intColumn = 1 to 1000
   If Sheet1.Cells(1, intColumn).Value2 = vbNullString then Exit For
   Sheet1.Cells(1, intColumn).Value2 = Int(Sheet1.Cells(1, intColumn).Value2) ' Do not use CInt but Int only!
next intColumn

Solution 3

Removing time from date for an entire data stored in an array

IMO this question rather asks how to change values directly in an array containing dates including time indications. Therefore I the following example code just as an addition to the valid solutions above demonstrating how to

• get a zero-based 1-dim array from a data column (without loops),
• execute the time removals (in a Loop) using VBA's Fix function *) to cut the decimal part of the date value and
• (optionally write back the array to any wanted column)

*) Using the CLng instead of Fix(or Int) would round up to the next day any dates with afternoon hours after midday.

Example code

Assumes date values e.g. in column B:B (omitting a title row).

Sub chngDateArray()
  Dim ws As Worksheet  
  Set ws = ThisWorkbook.Worksheets("MySheetName")                             ' << change to your sheet name string
  Dim lastRow&, i&, arr
  lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row     
' [1a] assign dates to (1-based) 2-dim datafield array
       arr = ws.Range("B2:B" & lastRow).Value2                                ' get date values as numbers
' [1b] rearrange to (0-based) 1-dim array
       arr = Application.Transpose(arr): ReDim Preserve arr(0 To UBound(arr) -1) ' make it a "flat" (zero-based) array
' [2]  eliminate time portions and reconvert to date
       For i = 0 To UBound(arr)    
           arr(i) = CDate(Fix(arr(i)))                                        ' cut time portions from entire dates
       Next i
       ' Debug.Print Join(arr, "|")                                           ' check in immediate window

' [3]  {optional}: write back array to any wanted column (e.g. D:D)
       ws.Range("D2").Resize(UBound(arr), 1).Value2 = Application.Transpose(arr)
End Sub

Additional note

Using .Value2 in section [1a] allows to get dates as numbers only. The reconversion to date format in section [2] eventually allows to write back the array data to any wanted column in section [3] without prior column formatting via NumberFormat.