I want to extract a date from a cell but it's formatted as mm/dd/yy so I can't get INT to work. The dates aren't the same length so LEFT or MID wont work correctly for the full data set. I have a sample of the date below. Is there any way to get the date out using a formula as there is roughly 3000 dates?

STEP2

STEP3

OUTPUT EXTRACTED DATES

CodePudding user response：

Date and Datetime in excel are stored as numbers based on an epoch datetime. MM/dd/yyyy, dd/MM/yyyy ... are simply different display formats for the same underlying number.

To split a datetime to date and time values in Excel, there are 2 basic ways:

1. Just copy the same value on to two columns and set only the format to show either just Date or just time (right click, format cells and select your desired date only or time only format).
2. Explicitly create date only and time only values via formulas. ie: Assuming your data starts at A1:

Date only formula:

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Time only formula:

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))