Purpose
Get the current date
Return value
Excel date as a serial number
Syntax
=TODAY()Using the TODAY function
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
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...
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...
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...
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...
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 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 calendar formula
Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will automatically return the first day of the current month. In this example, the goal is to generate a dynamic calendar for any given month, based on a start date entered in cell J6...
Dynamic calendar grid
Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month. With the layout of the grid as shown, the main problem is to calculate the date in the first cell in the calendar (B6). This...
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...
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 first day of month
In this example, the goal is to get the first day of the month based on any valid date. This problem can be solved by combining the EOMONTH function with simple addition. The EOMONTH Function The EOMONTH function returns the last day of the month, a given number of months in the past or future. For...
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...
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...
Invoice age and status
The goal is to calculate the correct invoice status ("OK", "Paid", or "Overdue") using the following rules: If there is an "x" in the "Paid" column, return "Paid". If there is not an "x" in the "Paid" column, and if the age is less than 31 days, return "OK" If there is not an "x" in the "Paid"...
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.
