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
Happy birthday message
In this example, the goal is to display a Happy Birthday message when a birthday in a given cell matches the current date. The core of the problem is to compare the given birthday to the current date while ignoring the year. There are two main ways to approach the problem. The first way is to use...
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...
Conditional formatting date past due
In this example, we want to apply three different colors, depending on how much the original date varies from the current date: Green if the variance is less than 3 days Yellow if the variance is between 3 and 10 days Red if the variance is greater than 10 days For each rule, we calculate a...
Last n days
In the image shown, the current date is August 19, 2019. Excel dates are serial numbers , so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the AND function , the first logical test checks to see if the date in B5 is greater than or...
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...
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...
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...
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...
Data validation date in next 30 days
Data validation rules are triggered when a user adds or changes a cell value. The TODAY function returns today's date (recalculated an on on-going basis). The AND function takes multiple logical expressions and returns TRUE only when all expressions return TRUE. In this case, we need to test two...
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...
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...
Calculate days open
In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still open. Because dates in Excel are just serial numbers...
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...
Last n months
In this example, the goal is to create a formula that will return TRUE if a date is in the last complete 6 month period, starting in the previous month. This means the date must fall between a calculated start date and end date, which requires two logical tests. The formula uses the AND function to...
Filter on dates expiring soon
In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was replaced. The...
TODAY function videos
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 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 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 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.
