TODAY Function – Google Sheets

The TODAY function is a bare-bones date function used to return the current day’s value. That is all it does when used on its own. However, this function helps build dynamic formulas that use the resultant date.

For example, you may have a policy of liquidating any inventory that has been in your warehouse for over five years. Assuming that you have the purchase date of the inventory pieces, you can use this formula to find the current age of the inventory on the exact date you run the formula. Additionally, you can run it for a certain number of days in the future or past by adding or subtracting days from it.

Video Explanation

thumbnail for the dates and times in formula video
Video Showing How to Use Dates and Times
thumbnail for how dates and times work in Google Sheets video
Video Showing How Dates and Time Work

Tip: The TODAY function returns just the date. If you want the date and time, use the NOW function, which operates in the same way but returns the time and date in the same value.

Purpose

The TODAY function returns the value of the current day. As further explained in how to use dates and times in Google Sheets formulas, the value is an integer but Sheets will typically display it as a date.

Syntax

=TODAY()

Note that you need the parenthesis even though there should be nothing in them. Therefore, the sole purpose of this is to irritate you and make you wonder why. There is no answer; just live with it!

Examples

Example 1 – Plain and Simple

Firstly, this is a simple example of the TODAY function.

FormulaDescriptionResult
=TODAY()Output today’s date (not really “today” but what today was I wrote this page!12/28/2016

Example 2 – Separating the values of a date and time.

Different date values examined using various functions.

Time valueTickmarkYEARMONTHDAYHOURMINUTESECONDFormatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
818991230000
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now the function extracts the hours and minutes.
  • 4 Typed in a date but spelled out. Writing out the date also works, but it won’t work if you write the day as “31st” instead of 31.
  • 5 If you only type in a time, the “zero” date is 12/30/1899.
  • 6 Entered with the NOW function. The output will constantly change in the linked Google Sheet.
  • 7 Entered with the TODAY function. The output will change daily in the linked Google Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 will increment the value in number 8 above by 1 day. In other words, “1=12/31/1899”. It is just a matter of how it is displayed, which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes, and seconds instead of days.
  • 11 To get near the current date, you need to start with 43,000 days away from 12/30/1899.

Example 3 – Using TODAY in formulas

As our last example, the TODAY function is commonly used in formulas to calculate the age of an object. Below is an example inventory listing. You could add conditional formatting to make every cell with a value greater than 5 have a pink background and red font to let you know that the inventory is obsolete.

Tip: Add the rounding function to display fewer decimals such as =ROUND(((TODAY()-A2)/365),0)

Tip: The DATEDIF function provides a more robust method of calculating age. You can customize it to output days, months, years, or a combination thereof.

 ABCD
1Item #Purchase DateAge – FormulaAge – Value
2Hang glider – red striped2/12/2012=(TODAY()-B2)/3654.882191781
3Stunt bike6/30/2014=(TODAY()-B3)/3652.501369863
3Flaming Sword4/2/2013=(TODAY()-B3)/3653.745205479

Live examples in Sheets

Go to this spreadsheet for the examples of the TODAY function shown above that you can study and use anywhere you would like.

Leave a Comment