Removing time from date for all data stored in the array

Question

Using Excel VBA, how to apply the formula Date(year(A1),Month(A1),Day(A1)) to all dates in the entire column? Currently, the data is presented in a date-time format (for example, 30/04/1995 9:35:00 AM), but I only need the date - 30/04/1995.

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

Answer 1

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

Excel stores date / time information as a floating point number. The days to the left of the decimal and fractions of the days (hh: mm: ss) are on the right. If you want to remove time information from the underlying data, then convert to Long and then to Date. In VBA:

 DateOnly = CDate(CLng(DateTime)) 

In worksheet formulas, just convert to Int and format as you like

 DateOnly = INT(DateTime) 

Hope that helps

Answer 2

All dates in Excel include time: date and time. It is not possible to save only a date or time in a cell only. The reason is that Excel stores them as numbers. Everything before the decimal point is the date and something after the decimal point is the time. Thus, even if you put the number 42000 in the cell (without anything after the decimal point) and changed the format of this cell to date, the value will remain December 27, 2014 (42,000 days after December 31, 1899) with the estimated time zero = 00:00:00 in the morning.

Since all numbers can 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 the cell to display only the date part or the time part, or both. So all you have to do is hide the time.

If you want to change all dates to zero after the decimal point, you will have to iterate over the numbers and change all the 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