How do you format a cell that contains text like `1st Jan 2022`

into a cell that Google Sheets can recognize as a date cell?

There are certain types of date formats that can prevent the automatic import of data into Google Sheets into dates. One recent data type I had to deal with was where the day field was an *abbreviated ordinal number* (i.e. 1st, 2nd, 3rd, 4th… etc) and the month was an *abbreviated month* (eg. Jan, Feb, Mar… etc) and even the year was abbreviated too (eg. 2022 = 22, 2023 = 23, etc).

So how can you transform this text field into a date field using Google Sheets?

Here was how I tackled this most recent date format change:

## Split Date Text Into Fields

The first thing I do when handling text fields that will be transformed to dates is to separate out each date section. Generally, there are three date sections to a date field: the day of the month, the month and the year and these can be in different orders depending upon your locale.

Also, each date section is split up by a delimiter. Delimiters are those characters that separate each date section.

Common delimiters you may know that are used with dates are `/`

, or `-`

, or `“ ”`

(space character).

Sometimes you may have different delimiters for each section, which makes things messy, but the technique for extraction should be the same: **separate out each date section.**

The *best function* to use to help split each date section out is to use the aptly labelled function `SPLIT(cell, delimiter)`

which takes two parameters: the `cell`

you want to perform the split operation on, and the *delimiter* character you want to use to perform the split.

Whenever you use the `SPLIT()`

function in Google Sheets be mindful that space will be needed after the function to allow for the output of the formula. For example, as dates tend to have 3 sections (day of the month, month and year) ensure there are 2 columns **after** where you are making the function call to allow for the `SPLIT()`

function to output its result.

Here’s an example demonstrating the result of the `SPLIT()`

function in Google Sheets:

A | B | C | |
---|---|---|---|

1 | 1st Jan 22 | ||

2 | 1st`=SPLIT(A1," ")` | Jan | 22 |

`SPLIT`

function separates out each result to a column to the right of the function cellOnce you’ve got the date text data extracted into each column then it becomes a little easier to work on each field separately to get what you need.

## Convert 1st To Number

As the day of the month field contains an abbreviated ordinal annotation (eg. “st”, “nd”, “th”) I need to remove these characters from the cell. To extract numbers from a field containing both numbers and text I like using the `REGEXEXTRACT(text, regular_expression)`

formula which takes 2 parameters the `cell`

you want to perform the extraction on and the `regular_expression`

to perform the extraction.

In my use case, the required formula for extracting the numbers will be `REGEXEXTRACT(cell, "\d+")`

where the regular expression `\d+`

means get all the digits would extract all the numbers from the ordinal day of month cell.

If I had instead used `REGEXEXTRACT(cell, "\d")`

(notice the removal of the `+`

sign after the `\d`

) then the regular expression would only capture one digit (the first digit). As some of the days of the month are double digits I need to extract *all digits*.

Here’s how this formula works on my current working example:

A | B | C | |
---|---|---|---|

1 | 1st Jan 22 | ||

2 | 1st`=SPLIT(A1," ")` | Jan | 22 |

3 | 1`=REGEXEXTRACT(A2, "\d+")` |

`REGEXEXTRACT`

enables the extraction of data according to the regular expression syntaxIf you want to perform any further numerical operations on the result from the `REGEXEXTRACT`

function you may need to multiply it by `1`

to **help convert the resulting text to a number**.

Next, I need to transform the abbreviated month to its corresponding number (eg. Jan = 1, Feb = 2, etc).

## Convert Month Name To Number

How do you convert a month to its corresponding number (eg. Jan = 1, Feb = 2, March = 3, etc)?

The easiest approach I’ve found is to use the `SWITCH()`

formula. The `SWITCH(text, case, value,..., [default])`

takes at least 3 parameters, but each subsequent parameter added needs to be a pair of `case`

: `value`

pairs. Finally, you can set an optional `default`

value if no cases match the `text`

.

To have the `SWITCH()`

function output the number of the month in your spreadsheet if the month is abbreviated write your `SWITCH()`

formula accordingly:

`=SWITCH(cell, "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12)`

Or if your month cell is the full month name then you could change your `SWITCH()`

formula like so:

`=SWITCH(cell, "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12)`

As you can see you can easily cater the `SWITCH()`

formula according to your needs on how the month is written in your case.

Here’s an example of how this would look using the working example in this post:

A | B | C | |
---|---|---|---|

1 | 1st Jan 22 | ||

2 | 1st`=SPLIT(A1," ")` | Jan | 22 |

3 | 1`=REGEXEXTRACT(A2, "\d+")` | 1`=SWITCH(B2, "Jan", 1, "Feb", 2)` |

`SWITCH`

function provides an easy way to convert a text to a numberIn cell `B3`

above I didn’t enter all the months of the year to conserve space, but in your spreadsheet, you would.

The easiest task is left to last, converting the `YY`

abbreviation to a full year.

## Convert `YY`

To Full Year

To convert an abbreviated year, such as `22`

to the full year `2022`

you can either add `2000`

to your number or if there are years in the 1900s in your data set (eg. 98 = 1998) then you may want to use a simple `IF()`

function.

If you do have dates that can represent the previous century you need to determine at what point those years will represent the current century. For example, as we progress through time will `51`

mean `1951`

or `2051`

?

Whatever your use case for the dates your formatting make sure you know the limitation of your `IF`

function. This might certainly be an issue when dealing with birthdates.

A simple `IF`

function to help those cases where numbers are from the previous century could look something like this:

`=IF(cell > YEAR(NOW())-2000, 1900, 2000) + cell`

The way the above formula is structured it fetches the current year and subtracts `2000`

. It then compares this result to the `cell`

that contains the abbreviated year. If `cell`

is greater than this number then the assumption will be that the `cell`

refers to the previous century.

My use case was a lot simpler, therefore, to solve my issue I simply had to add `2000`

to the number:

A | B | C | |
---|---|---|---|

1 | 1st Jan 22 | ||

2 | 1st`=SPLIT(A1," ")` | Jan | 22 |

3 | 1`=REGEXEXTRACT(A2,"\d+")` | 1`=SWITCH(B2,"Jan",1,"Feb",2)` | 2022`=2000+C2` |

Now that you have the numerical representation of each date section you can easily wrap this up using one final formula: `DATE()`

.

## Convert Day, Month & Year Numbers Using `DATE()`

Formula

The next step to this process is to use the `DATE(full_year, month, day_of_month)`

which takes 3 parameters with the first being the numerical representation of the year, the second parameter being the numerical representation of the month (Jan = 1, Feb = 2, etc) and the third parameter representing the full year.

In my use case from the working example the `DATE()`

formula would then have the following inputs:

`=DATE(C3, B3, A3)`

Where `A3`

represents `1`

, `B3`

represents `1`

and `C3`

represents `2022`

which produces the date result of `01/01/2022`

.

## Wrapping Into One Function

Could you wrap this all up into one large function? Of course!

You may want to wrap this all into one function as you may not have all the columns or rows available to separate each step out. I would encourage you to make the room and to individually work on each date section if you’re getting errors or the final date result isn’t matching your expectations.

The only **new** function to help combine all the other functions into one that allows you to operate on the `SPLIT`

result is the `INDEX`

function. This will allow you to work on the individual sections returned by `SPLIT`

.

As an example, to operate on the day of the month section **only** I would use the `INDEX`

function by passing into the first parameter the `SPLIT`

formula, but then for the second parameter of the `INDEX`

function, I only want the first date section (as this corresponded to the day of the month). It would look something like this:

`=INDEX(SPLIT(A2, " "), 1)`

This combination of formulas will allow you to use the result as observed in cell `A2`

. Therefore, you can apply the formula used in `A3`

and wrap this into the formula you used to extract the day of the month number, therefore, the combined formula would look like so:

`=REGEXEXTRACT(INDEX(SPLIT(A2, " "), 1), "\d+")`

Next, to operate on the month section **only** use the `INDEX`

formula again to help extract from the `SPLIT`

formula the month section, you would use the following formula (as column `2`

output the month section):

`=INDEX(SPLIT(A2, " "), 2)`

Then with that result insert it into your `SWITCH`

formula like so:

`=SWITCH(INDEX(SPLIT(A2, " "), 2), "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12)`

Next, to operate on the year section **only** using the `INDEX`

formula this would be (as column `3`

output the year section):

`=INDEX(SPLIT(A2, " "), 3)`

Which when used with my use case in handling the full year would be:

`=2000 + INDEX(SPLIT(A2, " "), 3)`

If you were using an `IF()`

formula instead then your use case may look something like this:

`=IF(INDEX(SPLIT(A2, " "), 3) > YEAR(NOW())-2000, 1900, 2000) + INDEX(SPLIT(A2, " "), 3)`

Finally, each section would need to be inserted into the final `DATE()`

formula to transform the numbers into a date, therefore, the final function would look like this:

```
=DATE(2000 + INDEX(SPLIT(A2, " "), 3),
SWITCH(INDEX(SPLIT(A2, " "), 2), "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12),
REGEXEXTRACT(INDEX(SPLIT(A2, " "), 1), "\d+"))
```

I used carriage returns in the formula to help separate out each `DATE`

parameter to somewhat help with readability. Also, cell `A2`

refers to the original text date string (eg. `1st Jan 22`

).

## Summary

To convert a text field represented as a date in the format of abbreviated ordinal number for the day of the month (eg. 1st, 2nd, 3rd, etc); abbreviated month (eg. Jan, Feb, Mar, etc) and abbreviated year (eg. 22 for 2022) you can use the following formula where cell `A2`

references the original date text:

```
=DATE(2000 + INDEX(SPLIT(A2, " "), 3),
SWITCH(INDEX(SPLIT(A2, " "), 2), "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12),
REGEXEXTRACT(INDEX(SPLIT(A2, " "), 1), "\d+"))
```