CODEWITHSUNDEEP
dategoogle-sheetstextformatdate-format
ischurek
ischurek

Reputation: 15

How to convert text to date format in google sheet?

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

Upvotes: 0

Views: 2129

Answers (2)

kirkg13
kirkg13

Reputation: 3010

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.

enter image description here

Upvotes: 1

Peter Thoeny
Peter Thoeny

Reputation: 7616

Use the DATEVALUE() function on a date string, then use DATEDIF() to find the difference between two dates.

=DATEDIF(DATEVALUE("Oct 01, 2020"), DATEVALUE("Dec 25, 2020"), "D")

UPDATE: To find the date between today and a date string in another cell use this example:

=DATEDIF(DATEVALUE(A2), NOW(), "D")

If cell A2 contains string Oct 01, 2020, it will return 70 for today 2020-12-10

Upvotes: 1

Related Questions