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

REGEXEXTRACT
enables the extraction of data according to the regular expression syntax
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)

SWITCH
function provides an easy way to convert a text to a number
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+"))