- 1 Excel Date Formula Examples
- 1.1 Calculate Number of Days
- 1.2 Get the Current Date
- 1.3 Get Year, Month, Week or Day Number
- 1.4 Get Month or Day Name
- 1.5 Get End of Month Date
- 1.6 Get Date Value With INT
- 1.7 Get Weekday Number
- 1.8 Get Week Number
- 1.9 Find Week Start Date
- 1.10 Dates in GetPivotData formula
- 1.11 Get Date From Text String
- 1.12 Get the Sample File
- 1.13 Related Tutorials
- 1.14 Don’t Miss Our Excel Tips
Excel Date Formula Examples
How to show month name or weekday for a date, first or last day of the month, week start date, subtracting dates, and more Excel date formulas. Get the free workbook with examples.
There are specialized date formulas on these pages: Leap Year Calculations, Easter Date Calculations, and see how to find the Nth Weekday in Month (e.g. the 4th Thursday)
And if your dates won’t change format, see the Date Format Troubleshooting Tips page.
Calculate Number of Days
To calculate the number of days between two specific dates, you don’t
need a special function — just subtract the earlier date from the later
Excel dates are really stored as numbers, so you can subtract one date number from another date number.
However, the result cannot be a negative number, so subtract the earlier date (smaller number) from the later date (bigger number).
Subtract Dates in Excel
In this example, there is a January start date in cell A2 and a June end date in cell B2.
To find the number of dates between the start date and end date, use
this formula in cell C2:
Tip: If the result looks like a date, instead of a number, change the
cell’s number format to General
Problem When Subtracting Dates
Sometimes when you’re working with date functions, the formula cell shows all number signs (hash tags, pound signs) instead of a number or date.
That happens if the result is a negative number — Excel dates must be zero or greater. Excel dates cannot be negative numbers.
In this example, the formula cell shows all number signs if you subtract the later date (B4) from the earlier date (A4). The result is a negative number, and Excel cannot display the result
Get the Current Date
To calculate the the current date, use the TODAY function. It will automatically
update, if you open the workbook on a different day.
NOTE: To enter the current date as a static value, that will not update,
use the shortcut key: Ctrl + ;
Get Year, Month, Week or Day Number
If a cell contains a date and time, you can use the following functions
to extract the year, month number, or day number from the date. In this
example, the date is in cell A2.
Week: = WEEKNUM(A2)
Day: = DAY(A2)
NOTE: If the result looks like a date, instead of a number, change the
cell’s number format to General
Get Month or Day Name
If a cell contains a date, you can extract parts of that date as text.
For example, show the month name, or the weekday name of the date. In
this example, the date is in cell A2.
Month Name (short): =TEXT(A2,”mmm”)
Month Name (long): =TEXT(A2,”mmmm”)
Weekday Name (short): =TEXT(A2,”ddd”)
Weekday Name (long): =TEXT(A2,”dddd”)
Get End of Month Date
If you need to calculate the first or last day of a month, the EOMONTH
(end of month) function works well. In this example, the date is in cell
- NOTE: For Excel formulas to find the nth weekday in a month, such as the 3rd Tuesday, go to the Nth Weekday in Month page.
The EOMONTH uses 2 arguments — Start Date, and Number of Months. To
get a date in the current month, use zero as the number of months.
Here are a few examples:
–Last day of previous month: =EOMONTH(B2,-1)
–First day of selected month: =EOMONTH(B2),-1)+1
–Last day of selected month: =EOMONTH(B2,0)
–First day of next month: =EOMONTH(B2,0)+1
–Last day of selected month, last year: =EOMONTH(B2,-12)
Or, combine the EOMONTH function with TODAY, to get dates relative to
the current date. For example:
–First day of current month: =EOMONTH(TODAY(),-1)+1
Get Date Value With INT
If a cell contains a combined date and time, you can extract just the
date value, by using
the INT function. Excel stores dates as numbers, with the integer
representing the date, and a decimal portion representing the time.
The INT function returns just the integer portion of that number, which
represents the date. In this example, the combined date/time is in cell
To get the date, enter the following formula in cell B2: =INT(A2)
Get Weekday Number
To get the weekday number for a date, use the WEEKDAY function. For example, with a date in cell B4, this formula will show its weekday number (Sunday = 1, Monday = 2, etc.):
WEEKDAY Return Type
There is an optional second argument for the WEEKDAY function — return_type — which controls how the weekdays are numbered. The list of options is shown in the screen shot below.
- If this argument is omitted, the default return_type of 1 is used, with weekdays numbered from Sunday (1) to Saturday (7)
- In older versions of Excel, only options 1, 2 and 3 are available.
Get Week Number
To get the week number for a date, use the WEEKNUM function. For example, with a date in cell B2, this formula will show its week number:
WEEKNUM Return Type
There is an optional second argument for the WEEKNUM function — return_type — which controls how the weekdays are numbered. The list of options is shown in the screen shot below.
- If this argument is omitted, the default return_type of 1 is used, with weeks starting on Sunday
- In older versions of Excel, only options 1 and 2 are available.
There are two systems used for the week numbers — System 1 and System 2. When you click on a Return Type in the list, you can see which system it uses.
System 1 — Week containing January 1 is the first week of the year, and is numbered week 1.
System 2 — Week containing first Thursday of year is first week of year, and is numbered as week 1. European week numbering system (ISO 8601)
Find Week Start Date
To group data by week, use the WEEKDAY function to calculate a week start date for each record. In the screen shot below, there are dates in cells A2:A9. The first 7 dates are all in the same week, with a start date of Sunday, November 27. The final date is in the following week, which begins Sunday, December 4.
Use this formula to calculate Sunday as the start date: =A2-WEEKDAY(A2+1,3)
The “3” at the end of that formula tells Excel to use the numbers 0 – 6 for Monday to Sunday. The date is adjusted by one day, to subtract 0 if the date is a Sunday, 1 on Monday, etc.
Dates in GetPivotData formula
If you’re using dates with the GETPIVOTDATA formula, you might have problems with
date references. If the date format in the formula is not an exact match
for the date format in the pivot table, the result might be an error.
To help you avoid this problem, this short video shows a few Date workarounds for the GetPivotData function. There are more details and written steps on the GETPIVOTDATA
Get Date From Text String
Excel’s date functions won’t work if the value is a text string, instead of a real date. For example, imported data might have dates in this format — YYYYMMDD.
To get the date from that string (it might be formatted as text or a number), you can use:
— LEFT, MID and RIGHT functions (traditional, longer formula)
— TEXT function (short formula)
The most common way to get the date from a YYYYMMDD string or number, is to use the DATE function, with LEFT, MID, and RIGHT extracting the year, month and day numbers.
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))
This video shows how to extract the year, month and day into separate columns, using these 3 formulas:
- Year: =LEFT(A2,4)
- Month: =MID(A2,5,2)
- Day: =RIGHT(A2,2)
Instead of using the long LEFT, MID, RIGHT formula, UniMord shared a formula that’s much shorter.
In that short formula:
- TEXT function formats the 8-digit YYYYMMDD number as “0000-00-00”
- the 2 minus signs convert the result to a positive number.
NOTE: This might not work for all regional settings, so test carefully before you use it in your workbooks. Unimord based this formula on a StackOverflow answer from Ron Rosenfeld.
Get the Sample File
To see how these formulas work, download the sample Date
Functions workbook. The file is zipped, and is in xlsx file
Leap Year Calculations
Split Dates and Times
Excel Dates Fix Format
Nth Weekday in Month
COUNT / COUNTIF
SUM / SUMIF
Dates and Times FAQs
Don’t Miss Our Excel Tips
Don’t miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.