How can you create a unique ID with date values in Google Sheets?
To create a unique ID of date values in Google Sheets use the
TEXT()
function to change the date into a string and then append any other useful identifier to that string to make the value unique, such as
ROW()
or a counter such as
COUNTIFS()
.
Creating a unique ID for your data rows can help speed up the processing of your data, for example when using the VLOOKUP formula. If you can create a column with unique ID’s you could then make the fourth paramter of the VLOOKUP function TRUE which speeds up processing time.
Depending upon your needs either of the two approaches below should help create unique ID’s for your data set.
Here are some examples that can help you create a unique column of ID’s when you have a column of data containing dates.
Date With Counter
One method for making a unique ID using dates especially when the current order of your rows is important is to append a counter.
Take for example the following list of dates in this spreadsheet:
| A | B | |
|---|---|---|
| 1 | Date (MM/DD/YYYY) | Unique IDs |
| 2 | 10/01/2022 |
202210011
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A2,A2)
|
| 3 | 10/02/2022 |
202210021
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A3,A3)
|
| 4 | 10/02/2022 |
202210022
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A4,A4)
|
As you can see from the result in column B above, the unique ID created appends a counter on the number of times the date already occurs in the list. Cell
B4
has the number 2 appended to the date string in
YYYYMMDD
format because it is the second date in column A that is the same.
This method is helpful when you have a large data set and don’t want the unique ID to grow too long. Depending upon your data source, you may only find your unique ID string growing to as much as 9 or 10 characters long.
You could even shorten the string further by changing the format of the date string to
YYMMDD
if the full year is not that important. This should then make the maximum length of your unique ID 7 or 8 characters.
If you do want to keep the length of your unique IDs the same, to help with sorting by the column , then change the formula from:
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A2,A2)To this:
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A2,A2),"00")
Just change the number of
0
symbols according to how many rows you’re likely to have of the same date. If you don’t think you’ll have any more than 10 rows, then you could just make it one
0
, or if you think you could have more than 10 but less than 100, then you would just make it two
00
. If you have more than 100 but less than 1000 rows containing the same date then make it
000
.
This would produce the following result with our current spreadsheet:
| A | B | |
|---|---|---|
| 1 | Date (MM/DD/YYYY) | Unique ID With Fixed Length |
| 2 | 10/01/2022 |
2022100101
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A2,A2),"00")
|
| 3 | 10/02/2022 |
2022100201
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A3,A3),"00")
|
| 4 | 10/02/2022 |
2022100202
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A4,A4),"00")
|
If keeping a short unique ID isn’t going to be an issue for you, then you could look at making an even simpler unique ID by appending the function
ROW()
.
Date With Row Number
Another easy way of creating a unique ID using dates in Google Sheets is to just append the row number after the date string. This is the easiest way of making a unique string but does mean according to your data set that the unique
Here is an example of appending the row number at the end of the string with the
ROW()
function:
| A | B | |
|---|---|---|
| 1 | Date (MM/DD/YYYY) | Unique IDs |
| 2 | 10/01/2022 |
202210012
=TEXT(A2,"YYYYMMDD")&ROW(A2)
|
| 3 | 10/02/2022 |
202210023
=TEXT(A2,"YYYYMMDD")&ROW(A3)
|
| 4 | 10/02/2022 |
202210024
=TEXT(A2,"YYYYMMDD")&ROW(A4)
|
This approach may appear to be the easiest, but the problem will be the unique IDs will grow in length as you eclipse row numbers 10, 100, 1000, 10000 (etc). This means your unique ID will grow from 9 characters in length to 13 characters in length if you’ve got more than 10000 rows.
This approach does come in handy as it helps to identify if there are any dirty data points that need to be cleaned up. By having the row number in the ID it can be easy navigating to the offending row.
To make the ID of the same length, to help with sorting by the unique ID column, use the
TEXT()
function with the appropriate number of
0
characters to ensure the row number will have the same length.
Modify the formula as follows where the characters
0000
represent the largest number of rows in the data set:
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A2),"0000")| A | B | |
|---|---|---|
| 1 | Date (MM/DD/YYYY) | Unique ID With Fixed Length |
| 2 | 10/01/2022 |
202210010002
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A2),"0000")
|
| 3 | 10/02/2022 |
202210020003
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A3),"0000")
|
| 4 | 10/02/2022 |
202210024004
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A4),"0000")
|
Summary
To create a unique ID using dates in Google Sheets use the
TEXT()
function to change the format of the date string to something consistent like
YYYYMMDD
or, if it’s not that important to maintain the full year (if you don’t have dates less than the year 2000), an abbreviated year
YYMMDD
and a counter wrapping it also in
TEXT()
to maintain consistent length using the
COUNTIFS()
function.
Another approach can be to append the row number by using the
ROW()
function, but if you have a large data set this will mean your ID will increase in length.
