How To Format Text Like “1st Jan 22” To A Date In Google Sheets

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

Once 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+")

If 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)

In 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+"))