Date ranges in Google Sheets

You have two columns: one with the start dates and the second one with the end dates.

If you want to create a range of dates in a single column, you can’t do it by merging both cells into one, using the ampersand (&) operator.

Let’s take a look at what happens then:

Both dates are converted into numbers. It happens because dates in Google Sheets are numbers that are formatted as dates. The values inside cell C2 are the number of days since 1/1/1900.

Formatting the cell into a date (Format >> Number >> Date) won’t change it. It still going to be a number.

Create a range of dates

Now it’s time to figure out how to create a range of dates, not a range of numbers.

For this operation, we are going to use the TEXT function.

It takes two parameters:

• value, which is a date
• date formatting

With the help of this function, we can create a date range.

The following formula will create the range with the same formatting as in columns A and B.

Of course, we can change the formatting to something different.

Handling missing dates

So far, so good, but the data is often not perfect and it’s a good idea to handle such exceptions as start or end date missing.

If you use this formula with missing dates, our example is going to look like this:

Let’s see what happened here. In both cases, we have the last day of 1899.

Remember that Sheets start counting from the first day of the year 1900, so 0 is 12/30/1899, -1 is 12/29/1899, etc. The empty space is treated as 0, so here you go.

To handle this exception, we are going to use the IF function inside our formula.

This formula will check whether the cell with data is not empty. If the condition is met (cell is not empty), it will display the formatted date, otherwise, it’s going to display an empty space. There are two conditions for both: the start and end dates.