Skip to Content

Create Unique ID With Dates: Google Sheets

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.

How to Create an Online Data Entry ...
How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets

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:

AB
1Date (MM/DD/YYYY)Unique IDs
210/01/2022202210011
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A2,A2)
310/02/2022202210021
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A3,A3)
410/02/2022202210022
=TEXT(A2,"YYYYMMDD")&COUNTIFS($A$1:A4,A4)
Create a unique ID using date field and counter

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:

AB
1Date (MM/DD/YYYY)Unique ID With Fixed Length
210/01/20222022100101
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A2,A2),"00")
310/02/20222022100201
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A3,A3),"00")
410/02/20222022100202
=TEXT(A2,"YYYYMMDD")&TEXT(COUNTIFS($A$1:A4,A4),"00")
Create a unique ID using date field and a counter maintaining consistency with the length of the ID to help with sorting by this column

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:

AB
1Date (MM/DD/YYYY)Unique IDs
210/01/2022202210012
=TEXT(A2,"YYYYMMDD")&ROW(A2)
310/02/2022202210023
=TEXT(A2,"YYYYMMDD")&ROW(A3)
410/02/2022202210024
=TEXT(A2,"YYYYMMDD")&ROW(A4)
Create a unique ID using date field and row number

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")
AB
1Date (MM/DD/YYYY)Unique ID With Fixed Length
210/01/2022202210010002
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A2),"0000")
310/02/2022202210020003
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A3),"0000")
410/02/2022202210024004
=TEXT(A2,"YYYYMMDD")&TEXT(ROW(A4),"0000")
Create a unique ID using row number with a specific set of numbers to help with sorting by this column

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.