Converting Dates With Decimal Points

Question

I downloaded a file with dates that have decimal points instead of forward slash:  For example, March 25, 2019 shows up as "2019.03.25" instead of "2019/03/25".   

Excel doesn't recognize the dates with decimal points (as far as I can tell).

Can the dates with decimal points be read as dates by Excel (2007)?  Or, can the dates with decimal points be converted to dates that can be read by Excel (2007)?

Answer 1

Alternatively: let's say you have such a value in A2.

Enter the following formula in B2:

=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

This formula can be filled down.

Answer 2

Select a column with such values.

On the Data tab of the ribbon, click Text to Columns.

Select Delimited, then click Next >.

Clear all check boxes, then click Next >.

Select YMD from the dropdown next to Date. This will automatically select the Date option button.

Click Finish.

Answer 3

If there is a time as well as a date in the cell, the formula should be

=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))

If you want to include the time:

=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))+TIME(LEFT(RIGHT(A2,8),2),LEFT(RIGHT(A2,5),2),RIGHT(A2,2))

Make sure that you format the cell with the formula as date + time, e.g.

yyyy/mm/dd hh:mm

Answer 4

This will definitely work.  Perfect.  Thank you.

Answer 5

Wow!  Fantastic!  Fast and helpful.

I tried, but it failed because there is a timestamp as well in the cell:  March 25, 2019 at 14:05 is "2019.03.25 14:05:00".  

Can you help with that?