excel - Remove hours and minutes from dates in one step

Question

I always get the dates like shown in Column A, but I need them as shown in column C. How can I do it without having to create column B helper? I need that because the hours and minutes mess up what I want to achieve after this.

Column A

Time with hours and minutes:

22/05/2015 14:57
11/06/2015 15:40
28/05/2015 08:27
26/05/2015 14:51
25/05/2015 14:18
25/05/2015 15:51

Column C

Time without hours and minutes:

22/05/2015 
11/06/2015 
28/05/2015 
26/05/2015
25/05/2015
25/05/2015

I managed to solve this by creating a column B that converts to text

=Text(A2;"DD-MM-AAAA")

and then column C converts text to value

=Data.value(B2)

While this process works, it causes me a lot of unnecessary data and I wonder if I can do this automatically with a vba-excel macro in one step. I also want the number dates in column C always equal to the number of dates in column A and dates in the same order.

Answer 1

To get the date only use:

=INT(A2)

And format to date. This works because dates in excel are the number of days since December 31st, 1899 so for example May 10th, 2016 is 42500 days since.

And the Time is a decimal based on 24 hours. So, May 10th, 2016 12:00:00 equates to 42500.5.

So to get just the date we remove the decimal. INT() does just that.