In this post we will see how we could extract date number from a date string.
In most of the practical purpose we get the date in a malformed way and we could not perform any logical operation because it doesn’t comply with any standard format.
Let’s see the below date values in column A, as you can see we have dates available as text string.
These dates are text in nature and we want to extract day from these date strings in excel as shown below. We could leverage excel’s built in function DATE(year,month,day) , to create a date in a standard format once we have day, year and month in three different column.
In the figure below, we want to have the day from date string in column B.
We will use excel ISNUMBER and text functions MID and FIND to extract the day number from these date strings.
Enter the formula =IF(ISNUMBER(1*MID(A1,FIND(” “,A1)+2,1)),MID(A1,FIND(” “,A1)+1,2),MID(A1,FIND(” “,A1)+1,1))*1 in cell B1 to get the day number in column B.
Hit Enter after entering the formula and you could see we have got the day number from date string in excel.
Now drag the formula below to get the date number for all the date strings.
Hope this helped.
