Extract Day From Date String In Excel

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.

table showing date 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.

table showing day number  extracted from date string

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.

excel formula to get day number from date string

Hit Enter after entering the formula and you could see we have got the day number from date string in excel.

day number extracted from date string




Now drag the formula below to get the date number for all the date strings.

drag formula down to get all the day number from date string

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply