How to convert text to date format in google sheet?

Question

In my google sheet I have a column with dates but its in a text format. here an example what I have:

Oct 01, 2021
Dec 25, 2020
...
...

I want to convert it to a date format

01/10/2021
25/12/2020
....

I need to find the number of days from the dates in this column, by using "date in column" - now(). This does not work with the format "Oct 01, 2021" since its a text, and I am getting an error from Googlesheet. Thanks in advance IS

Answer 1

Try this formula in F2:

=ARRAYFORMULA(IFERROR(DATEDIF(
      DATE(
        RIGHT(E2:E,4),
        MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
        MID(E2:E,5,2)), 
      NOW(), "D")))

Update

Revised the formula, which goes in F1 and fills the column, to:

={"Days Left";ARRAYFORMULA(
  IFERROR(-1 * DATEDIF( DATE( RIGHT(E2:E,4), MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0), MID(E2:E,5,2)), NOW(), "D"),
    IFERROR(DATEDIF( NOW(),DATE( RIGHT(E2:E,4), MATCH(LEFT(E2:E,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0), MID(E2:E,5,2)),  "D"))))}

which reverses the date difference values. It also handles date differences for dates either in the future, or in the past.