Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR

In this post, we’re going to look at some of the basic date functions and in particular, how we can extract parts of a date or a time.  We’ll cover: NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND.

---

Example 1 – Getting the current date and time

We can add the current date and time to our sheet with the very simple function, NOW.

Type the following:

=NOW()

This will add the current date and time in the cell.

This doesn’t update every second, but by default will update every time there’s a change made on the sheet. We can alter this by changing the recalculation settings. Go to “File” then “Spreadsheet settings”.

Under “Calculation”, click the drop-down menu that says “On change”.

Here you will have three options. Choose the one you want, then click “Save settings”.

---

Example 2 – Getting today’s date and using it in calculations

Similar to above, we can get today’s date by using the TODAY function. Type the following:

=TODAY()

This adds today’s date in the cell.

We often use this function in calculations. For example, let’s find out how many days are left until Christmas day.

In cell B1 I have Christmas day and in cell C1 I type the following:

=B1–today()

This just subtracts Christmas day from today’s date and returns the number of days, which at the time of writing, there are 213.

We can improve the returned result by adding some text to show what it is.

We start with the same formula as before, then add an ampersand and within inverted commas, we add the text we want.

=B1–today()&” days to Christmas”

As we can see, this adds the number of days to the text. This will count down every day.

---

Example 3 – Extracting the day from a date

Sometimes we want to extract a particular part of the date, to find out some piece of information. As an example, here we have the number of students that signed up for courses. The courses start either on 1st of the month, or on 15th. The Marketing department want to know which is more popular, the 1st or the 15th. From the data, it’s difficult to see.

So, first we need to highlight which days are the 1st and which 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 down the rows.

=day(A2)

This has extracted just the days from the dates in column A.

Now we can add up those which are the 1st and those which are on the 15th.

To do this we use a SUMIF function (see my post on this). Write the following in cell F1:

=sumif(C2:C13,“=1”,B2:B13)

This looks in range C2:C13 and checks to see if there are any “1s”, if there are it adds up the number of students in column B, that correspond. Similarly, we do the same for “15ths”.

We can see above that the 15th has more students.

---

Example 4 – Extracting the month or year / Find out how old someone is

Similar 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 birthday. In cell B1, I’ve added the MONTH function 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’ve added the YEAR function you can see in cell C2, and this returns the year.

Let’s find out how old this person is. In cell B3, I’ve written the formula you can see in cell C3. This gets the year from today’s date and then subtracts the year in cell A1, i.e. 2016-1973. It then returns the number of years.

=MONTH(A1)

=YEAR(A1)

=YEAR(TODAY())-YEAR(A1)

Similar to example 2, we can add some text to the number to make it more meaningful, by using the ampersand.

=YEAR(TODAY())-YEAR(A1)&” Years Old”

The same applies for extracting times from a date and time. We have the HOUR, MINUTE and SECOND function which will extract the different parts of a time.

As you can see, by themselves these functions are limited but when combined with other functions, they allow you to work with dates and calculate what you want.

---