DATE Function – Create Date in Excel, VBA, Google Sheets

This tutorial demonstrates how to use the Excel DATE Function in Excel to create a date.
DATE Main Function

DATE Function Overview

The DATE Function Returns a date from year, month, and day.

To use the DATE Excel Worksheet Function, Select cell and Type:

date formula syntax

(Notice how the formula input appear)
DATE Function syntax and Inputs:

year – A year greater than 1900 (1904 for Macs). Example: 2010.

month – The corresponding number of the month you wish to use (1-12). The month number can also be negative or greater than 12. Example: 2.

day – The day of the month you wish to use (1-31). The day number can also be negative or greater than 31. Example: 18.

DATE Examples

The DATE Function is used to create dates by entering a year, month, and day:

DATE

Day and Month values can be zero:

DATE DM ZERO

or negative:

DATE DM Negative

AutoMacro - VBA Code Generator

DATE – DAY, MONTH, YEAR Functions

Often the Date Function is used along with DAY, MONTH, or YEAR Functions to calculate a date based on another date.

This example will return the first day of the year based on a given date:

First day of the year

Date Serial Numbers

The DATE Function will return a serial number representing a date:

DATE Serial number
DATE in Google Sheets
The DATE Function works exactly the same in Google Sheets as in Excel:

Date Google sheet

Additional Notes

Use the DATE Function to create a date serial number. Just enter numbers corresponding to the month, day, and year. You can actually enter months greater than 12 or less than 1 and Days greater than 31 and less than 1. Entering month=13 will set the date to January of the following year. Month=0 will set the date to December of the previous year.

Examples:
=date(2016,0,0) returns 11/30/2015.
From this starting point you can add or subtract months and days.

=date(2016,13,1) returns 1/1/2017

=date(2016,-5,1) returns 7/1/2015

The result of the DAY Function may appear as a serial number. Change the cell Number Formatting to Short Date to display the serial number as a date:
date formatting excel shortcut

DATE Examples in VBA

You can also use the DATE function in VBA. Type:
application.worksheetfunction.date(year,month,day)
For the function arguments (year, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!