Purpose
Get the current date
Return value
Excel date as a serial number
Syntax
=TODAY()How to use
The TODAY function returns the current date, and will continually update each time the worksheet is updated. Use F9 to force the worksheet to recalculate and update the value.
The value returned by the TODAY function is a standard Excel date. To display the result as a date, apply a date number format. Optionally customize the number format as you like. If you want the current date with a time value, use the NOW function.
Examples
The TODAY function can be used on its own, or combined with other functions. The formulas below show how the TODAY function can be used in various ways:
=TODAY() // current date
=TODAY()-7 // one week in past
=TODAY()+7 // one week in future
=TODAY()+90 // 90 days from today
=EDATE(TODAY(),3) // 3 months from today
=EDATE(TODAY(),12) // 1 year from today
=EDATE(TODAY(),-12) // 1 year in the past
=EOMONTH(TODAY(),-1)+1 // first day of current month
=TODAY()+TIME(18,0,0) // today at 6:00 PM
=TODAY()+1+TIME(12,0,0) // tomorrow at noon
Static date and time
If you need a static date and time that won't change, you can use the following shortcuts:
- Insert current date - Control + ;
- Insert current time - Control + Shift + :
To enter both values in a single cell, enter the date, a space, then the time.
Formatting results
The result of TODAY is a serial number representing a valid Excel date. You can format the value returned by TODAY using any standard date format. You can use the TEXT function to build a text message that includes the current date:
="The current date is "&TEXT(TODAY(),"mmm d")
To return a message like "The current date is May 31".
TODAY function examples
Data validation date in specific year
Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function. When a user enters a value, the YEAR function extracts and compares the year to 2016: =YEAR(C5)=...
Calculate days remaining
Dates in Excel are just serial numbers that begin on January 1, 1900. If you enter 1/1/1900 in Excel, and format the result with the "General" number format , you'll see the number 1. This means that you can easily calculate the days between two dates by subtracting the earlier date from the later...
Add days to date
In this example, the goal is to add days to a date. This is a frequent task in Excel when you need to calculate a new date by adding a specified number of days to an existing date. Here are some examples of situations where this might be useful: Calculate a due date by adding a given number of days...
Get days before a date
In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. Dates are valid through 9999, which is serial number 2,958,465. This means that January 1, 2050 is the serial number 54,789. In the example, the date is...
Working days in year
NETWORKDAYS is a built-in function accepts a start date, an end date, and (optionally) a range that contains holiday dates. In the example shown, we generate the start and end date using the DATE function like this: DATE(D5,1,1) // first day of year DATE(D5,12,31) // last day of year The DATE...
Get age from birthday
The DATEDIF function (Date + Dif) is a bit of an anomaly in Excel. A compatibility function that comes originally from Lotus 1-2-3, Excel will not help supply arguments when the function is entered. However, DATEDIF works in all modern versions of Excel and is a useful function for calculating the...
Working days left in month
NETWORKDAYS is a built in function accepts a start date, end date, and (optionally) a range that contains holiday dates. In this case, the start date is Jan 10, 2018, provided as cell B5. The end date is calculated using the EOMONTH function with an offset of zero, which returns the last day of the...
Dynamic date list
Dates in Excel are just serial numbers , formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past. In the example shown, the date in the named range "start" is provided by the TODAY function: =TODAY() //returns current date The...
Sum last 30 days
In this example, the goal is to calculate total amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. There are three basic approaches to solving this problem: (1) a traditional approach based on the SUMIFS function , (2) a more flexible approach that...
Get stock price last n months
In this example, the goal is to get monthly closing stock price over the past n months (i.e. last 6 months, last 12 months, last 24 months, etc.) for the list of symbols that appear in column B. In addition, we want a rolling time period, that stays in sync with the current date. This can be done...
Get stock price (latest close)
In this example, the goal is to retrieve the last available close price for each symbol shown in column B. This can be done with the STOCKHISTORY function . The main purpose of STOCKHISTORY is to retrieve historical stock price information, and we need to make a few adjustments to prevent errors...
Last updated date stamp
The TEXT function can apply number formatting to numbers just like Excel's built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel's cell formatting, the TEXT function works inside a formula and returns a result that is text. You can use TEXT to format numbers that...
Range contains specific date
First, it's important to note first that Excel dates are simply large serial numbers . When we check for a date with a formula, we are looking for a specific large number, not text . This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is...
Data validation allow weekday only
Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type argument...
TODAY function videos
How to check and debug a formula with F9
One thing you'll frequently do in Excel is check or debug formulas. In this video, we'll look at how to use the F9 key to quickly break a formula down into pieces that you can understand. Here we have a simple list of names. In addition to names, we have a column for birthdate, a column for age,...
How to use function arguments
You've probably noticed that functions use parentheses, and inside those parentheses are certain inputs. These inputs have a special name: arguments. Let's look at some examples. Arguments can be required or optional. Some functions take three or more arguments, and some functions don't take any...
How to display current date and time
In this video we look at several ways to handle current dates and times. You may often want to enter a current date and time into a worksheet. A simple way to do this is to enter a time or date stamp. You can do this using keyboard shortcuts. Control semicolon enters the current date. Control-Shift...
How to step through complex formulas using Evaluate
Excel has a handy feature called Evaluate Formula, which solves a formula one step at a time. Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result. Here's the same worksheet we looked at in a previous video when we talked about...
How to calculate and highlight expiration dates
In this video we'll look at how to calculate and highlight expiration dates. Let's say your company has started a membership program of some kind and your boss just sent you a set of data. She's given you a list of 1,000 people that have renewed a membership in the last year or so, and she's...
Related functions
NOW Function
The Excel NOW function returns the current date and time, updated continuously when a worksheet is changed or opened. The NOW function takes no arguments. You can format the value returned by NOW as a date, or as a date with time by applying a number format...
TEXT Function
The Excel TEXT function returns a number formatted as text. This makes it easy to embed a formatted number (e.g., dates, times, currencies, percentages, fractions, etc.) in a text string with the number format of your choice.
