Excel, Dates

Jun 11, 2015 by azurous in DATE

When working in Excel there are going to be times where you need to specify a date value. There are different methods for specifying a date in Excel. This article briefly explains them and provides links to more detailed explanations. Before explaining the different methods for inputting dates in cells I have explained the difference between a date value and a date expression.

Jump To:

Date Value Vs Date Expression
Method 1, Normal Text Expression
Method 2, Using the Date() Function
Method 3, Using the DATEVALUE() Function

–

Contents

Date Value Vs Date Expression:

So whats the difference between a date value and a date expression?

Date Expression: A date expression is a text string that represents a date. For example:

“January 23, 1990”.

When somebody sees a date expression they understand what it means. Date expressions are what you and I can understand. On the other hand, a date expression means nothing to the computer. If you input a date expression in a cell, Excel will have no idea what that means.

Date Value: A date value is basically a number. For example the date value associated with “January 23, 1990” is the number 32896. For me and you the number 32896 means nothing, while for the computer (Excel) 32896 has a meaning.

Excel provides several different functions which receive meaningful values from the user and converts them into Date Values:

By applying the correct date formatting to the cell, the date value is displayed in a format meaningful to the user:

–

Method 1, Normal Text Expression:

As explained in the previous section one method for specifying dates in Excel is using a normal text expression as you would in a text editor. As you can in the figure below the user is inputting date values in column A as he would in a text editor like notepad:

While this may seem like a very straight forward method for inputting dates, but it is not recommended. There are 2 main reasons why this method of inputting dates in not recommended.

Reason 1: As previously explained in the article Excel, DATEVALUE() Function, there are many built in functions in Excel which accept a date value as input. If you wish to be able to use those function, you must work with date values rather than date expressions.

Reason 2: Lets say we input dates in the format specified in the figure above. Should you decide to change the display format for the dates to something else, you will have to manually retype all the dates. For example if we wanted the dates to be displayed in the format below, the changes will have to be done manually:

–

Method 2, Using the Date() Function:

Another method for specifying a date value in Excel is using the Date() function:

Result:

I have provided a complete article which covers the Date() function:

Excel, Date() Function

–

Method 3, Using the DATEVALUE() Function:

Another method for inputting dates in Excel, is using the DATEVALUE() function. The DATEVALUE() function receives as input a text string representing a date and returns the associated date value:

Result:

I’ve covered this topic in detail in the article below:

Excel, DATEVALUE() Function