In this article, I am going to share some of the basic date functions in Google Sheets, specifically focusing on how we can extract parts of a date or time. We will cover: NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND.
Example 1 – Getting the current date and time
We can easily add the current date and time to our sheet using the NOW function. Just type the following:
=NOW()This will add the current date and time to the cell. Please note that it does not update every second, but by default, it will update whenever there is a change made to the sheet. You can modify this behavior by changing the recalculation settings. Go to “File” and then “Spreadsheet settings”.
Under “Calculation”, click on the dropdown menu that says “On change”.
Here, you will have three options. Choose the one you prefer and click “Save settings”.
Example 2 – Getting today’s date and using it in calculations
Similarly to the previous example, we can get today’s date using the TODAY function. Type the following:
=TODAY()This will add today’s date to the cell. We often use this function in calculations. For example, let’s see how many days are left until Christmas.
In cell B1, I have the date of Christmas, and in cell C1, I type the following:
=B1-TODAY()This simply subtracts the date of Christmas from today’s date and returns the number of days, which, at the time of writing this article, is 213.
We can enhance the returned result by adding text to show what it is. We start with the same formula as before, then we add an ampersand and in backticks, we add the text we want.
=B1-TODAY()&" days until Christmas"As we can see, this adds the number of days to the text. It will count down every day.
Example 3 – Extracting the day from a date
Sometimes, we want to extract a specific part of the date to find specific information. For example, here we have the number of students enrolled in courses. The courses start either on the 1st of the month or on the 15th. The Marketing department wants to know which one is more popular, the 1st or the 15th. From the data, it’s hard to tell.
So, first, we need to highlight which days are the 1st and which days are the 15th. We can do this by adding the following DAY function to column C. In cell C2, type the following, then copy it to the following rows:
=DAY(A2)This will extract only the days from the dates in column A.
Now, we can sum up the ones that are the 1st and the ones that are the 15th.
To do this, we use a SUMIF function (see my article on this). Write the following in cell F1:
=SUMIF(C2:C13,"=1",B2:B13)This looks in the range C2:C13 and checks if there are any “1”s. If there are, it sums up the number of students from column B that match. Similarly, we do the same for the “15”s.
We can see above that the 15th has more students.
Example 4 – Extracting the month or year / Finding someone’s age
Similarly to the DAY function, we can also use the MONTH and YEAR functions to extract the month and year from a date.
In cell A1, I have a date of birth. In cell B1, I added the MONTH function that you can see in cell C1. This returns the month as a number from 1 to 12.
We can do the same with the year. In cell B2, I added the YEAR function that you can see in cell C2, and it returns the year.
Now let’s find out how old this person is. In cell B3, I wrote the formula that you can see in cell C3. It takes the year of today’s date, then subtracts the year from cell A1, which is 2016-1973. Then it returns the number of years.
=MONTH(A1)=YEAR(A1)=YEAR(TODAY())-YEAR(A1)Just like in example 2, we can add text to the number to make it more meaningful, using the ampersand.
=YEAR(TODAY())-YEAR(A1)&" years"The same goes for extracting the time from a date and time. We have the HOUR, MINUTE, and SECOND functions that will extract the different parts of a time.
As you can see, on their own, these functions are limited, but combined with other functions, they allow you to work with dates and calculate what you want.
