Excel, Dates

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:

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:

Excel, User Input to Date Value

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

Excel, Date Output


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:

Excel Dates, Inputting, Text ExpressionWhile 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:

Excel, Date Format Text Expression


Method 2, Using the Date() Function:

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

Excel, Date Function, Example 4
Result:

Excel, Date Function, Example 4, Result
I have provided a complete article which covers the 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:

Excel, Functin, DateValue, Example Basics
Result:

Excel, Function, DateValue, Result
I’ve covered this topic in detail in the article below:

 

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *