Contextures

Excel Date Functions

How show month name or number, get first or last day of month, and more Excel date formulas. Get the free workbook with examples.

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

For example, with a start date in cell A2 and end date in cell B2, use this formula:

=B2-A2

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

subtract start date from end date

If you subtract the later date from the earlier date, the result will be a negative number, and Excel cannot display the result

error when subtracting end date from start 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.

=TODAY()

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

current date with TODAY function

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.

Year: =YEAR(A2)

Month: =MONTH(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

year month and day numbers

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")

year month and day name

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.

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

year month and day name

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)

if function checks for empty cell

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

weekday function

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.

week start date

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 function

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.

week number return types

  • 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)

week number return type system

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.

week start date

Dates in the GETPIVOTDATA formula

When working with the GETPIVOTDATA formula, some fields might require 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 avoid this, you can use one of the Date workarounds that are described on the GETPIVOTDATA page.

Download 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

30 Functions in 30 Days

COUNT / COUNTIF

SUM / SUMIF

Date Picker

Dates and Times FAQs

Get All the Excel News

For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

 

Custom Search

 

30 Excel Functions in 30 Days

 

 

 

excel tools

 

Last updated: July 13, 2017 7:44 PM