Home > Formulas > Date > Date Functions

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 Microsoft Excel date formulas. Get the free workbook with date formula in Excel 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 in November)

And if your dates won't change format, see the Date Format Troubleshooting Tips page.

Calculate Number of Days

If you wonder how to calculate date difference in Excel, it's just like calculating the difference between two numbers on an Excel worksheet.

• Excel dates are really stored as numbers
• You can subtract one date number from another date number.

To find the number of calendar days between two specific dates, you don't need a special function. Just subtract the earlier date from the later date, using the minus sign.

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 days between the start date and end date, use this formula in cell C2:

• =B2-A2

This formula starts with a cell reference to the end date in cell B2, and subtracts the start date in cell A2

Tip: If the result looks like a date, instead of a number, change the cell's number format to General

Notes

• The date subtraction formula result cannot be a negative number.
• Be sure to subtract the earlier date (smaller number) from the later date (bigger number).
• To fix formulas that show numbers signs, see the Date Cell Filled with Number Signs section, below.

Calculate End Date

You can also use a simple Excel formula to calculate end date, based on a start date and number of days.

In this example, there is a January start date in cell C1 and a number of days to add, in cell C3.

To find the end date, use this formula in cell C5:

• =C1 + C3

This formula starts with a cell reference to the start date in cell B2, and uses the plus sign to add the number of days from cell C3

Tip: Be sure to format cell C5 with a date format, such as Short Date.

Number of Working Days

To calculate the number of working days between two dates, there IS a special Excel function. You can use the NETWORKDAYS function.

The NETWORKDAYS function has 2 required arguments and one optional argument:

• =NETWORKDAYS(start_date, end_date, [holidays])

In this example, the project start date is in cell B2, and the project due date is in cell C2.

In cell B5, the following formula calculates the number of working days between those two dates:

• =NETWORKDAYS(B2,C2)

Include Holiday Dates

The optional 3rd argument for NETWORKDAYS is a list of holiday dates.

In the next example, the workbook has a list of holiday dates, and the list is a named range, MyHol.

• =NETWORKDAYS(B2,C2, MyHol)

Because there is a holiday within the date range, the formula result is one number lower.

Date Cell Filled with Number Signs

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.

Cell Too Narrow

In some cases, this happens because the cell is too small to show the date.

To fix that problem, make the column wider, so the formatted date will fit.

Negative Numbers

Another reason that date cells are filled with number signs is that a formula reurned a negative number, and the cell is formatted as a date:

• Excel dates must be zero or greater.
• Excel dates cannot be negative numbers.

For example, in the screen shot shown below, the formula cell C4 shows all number signs, instead of a number of days between the start date and end date.

This happened because:

• I accidentally subtracted the later date (B4) from the earlier date (A4).
• The result is a negative number
• Cell C4 was formatted as a short date

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. The TODAY function does not require any arguments.

• =TODAY()

NOTE: To enter today's date as a static value, that will not update, use the shortcut key: Ctrl + ;

Get Year, Month or Day Number

If a cell contains a date and time, you can use the following Excel date functions to extract the year, month number, or day number from the given date.

For each of these Excel functions, the argument syntax is the same. There is only one required argument:

• serial_number - Required

The serial number represents a date, which Excel stores as numbers.

• You could type a number as the argument, or use a cell reference.

This screenshot shows the MONTH function, with its syntax.

Find Year, Month or Day

In this example, there is a valid date in cell A2.

Use the following date functions to return the Year, Month and Day for the specific date.

Year: To get the year from the date in cell A2, use the YEAR function:

• =YEAR(A2)

Month: To get the month of the year number from the date in cell A2, use the MONTH function:

• =MONTH(A2)

Day of Month: To get the day of the month from the date in cell A2, use the DAY function:

• = DAY(A2)

NOTE: If the formula 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, by using the Excel TEXT function.

This screenshot shows the TEXT function, with its argument syntax. There are 2 required arguments:

• value: number to convert to text
• format_text: text string that defines for the number formatting to use

For example, show the month name, or the day of the week name for 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 B2.

• 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

Add or Subtract Months with EDATE

Use the EDATE function to add or subtract a specific number of months, based on a start date.

• EDATE adds or subtracts the specified number of months to the start date and tries to return the same day of the month.
• If the same day in the calculated month is larger its end of month date, EDATE will return the end of the month date.

The Excel EDATE function has two arguments in its syntax, and both arguments are required:

• = EDATE(start_date, months)

EDATE Examples

In the EDATE examples shown in the screen shot below:

• The start date, in cell C2, is January 29th.
• In cell B5, the number of months is 1
• The formula in cell C5 is: 
  • =EDATE($C$2, B5)

The EDATE formula result is 1 month after the start date, but February 29th is not a valid date -- the month only has 28 days.

So, the EDATE function returns the last date in February - February 28th.

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 A2.

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, based on the default numbering system (Sunday = 1, Monday = 2, etc.):

• =WEEKDAY(B4)

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(B2)

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.

WEEKNUM Systems

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 page.

Video Timeline

• 00:00 Get data from pivot table
• 00:37 Edit the Formula
• 00:48 Problem with Dates
• 01:27 Match the Row Label
• 01:45 Use DATEVALUE Function
• 02:15 Use DATE Function
• 02:43 Date in Worksheet Cell

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)

Get Date with LEFT, MID, Right Functions

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)

Get Date with TEXT Function

Instead of using the long LEFT, MID, RIGHT formula, UniMord shared a formula that's much shorter.

=--TEXT(A2, "0000-00-00")

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 format

Related Tutorials

Functions List

WORKDAY.INTL function

Leap Year Calculations

Dates, Weekend

Split Dates and Times

Excel Dates Fix Format

Nth Weekday in Month

COUNT / COUNTIF

SUM / SUMIF

Date Picker

Dates and Times FAQs