TODAY Function - Google Sheets
The TODAY function is a bare bones date function used to return the value of the current day. That is all it does when used on its own. However, this function is useful in building 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, this formula could be used to find the current age of the inventory on the exact date the you run the formula. You can always run it for a certain number of days in the future or past by adding or subtracting days from it.
Tip: The TODAY function returns just the date. If you want the date and time, usethe NOW functionwhich operates in the same way but returns the time and date in the same value.
The TODAY function returns the value of the current day. As further explained inhow to use dates and times in Google Sheets formulas, the value is actually an integer but will typically be displayed as a date.
Note that you need the parenthesis even though there should be nothing in them. The sole purpose of this is to irritate you and make you wonder why. There is no answer, just live with it!
Example 1 - Plain and Simple
A simple example of the TODAY function.
|Output today's date (not really "today" but what today was when this was written!||12/28/2016|
Example 2 - Separating the values of a date and time.
Different date values examined using various functions.
|Time value||Tickmark||YEAR||MONTH||DAY||HOUR||MINUTE||SECOND||Formatted as number|
|6/1/2017 2:32 PM||3||2017||6||1||14||32||0||42,887.61|
|July 31, 2016||4||2016||7||31||0||0||0||42,582.00|
- 1A random date typed in using slashes.
- 2A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
- 3Typed a random date in and added a time. Now notice the hours and minutes being extracted.
- 4Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
- 5If you only type in a time, the "zero" date is 12/30/1899.
- 6Entered with theNOW function. This will change constantly in thelinked Google Sheet.
- 7Entered with theTODAY function. This will change daily in thelinked Google Sheet.
- 8A blank cell is December 30, 1899. Similar to tickmark 5.
- 9Typing a 1 increments the value in number8above 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.
- 10Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
- 11To get near the present date, you need to start with 43,000 days away from 12/30/1899.
Example 3 - Using TODAY in formulas
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 that has 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 less decimals such as
Tip: TheDATEDIFfunction provides a more powerful method of calculating age. It can be customized to output days, month, years, or a combination thereof.
|1||Item #||Purchase Date||Age - Formula||Age - Value|
|2||Hang glider - red striped||2/12/2012||=(TODAY()-B2)/365||4.882191781|
Live examples in Sheets
Go tothis spreadsheetfor the examples of the TODAY function shown above that you can study and use anywhere you would like.