Extract and present information about dates in Excel

Q. I receive Excel spreadsheets regularly that contain dates. I have to manually look up the day of the week that represents these dates. Are there any functions that can help speed this process along?

A. There are several ways to extract and present date information. We will look at a few ways to extract and present information about the day, month, and then year. To help walk through this process, you can download an Excel workbook and access an accompanying video at the end of this article.

Extract day information

You have a few options to extract and present information about the day. Let’s start with using the DAY function. The syntax for DAY is DAY(serial_number), where serial_number is the cell that contains the date. You can see the formula in cell C3 of the screenshot below.

Next, we will use the TEXT function. The syntax for TEXT is TEXT(Value, Format_text). The first argument, Value, is the value, in this case the date, that we want to format. The second argument, Format_text, is the format that we want to apply to the text.

In order to extract the day and present it as the three-letter abbreviation, define Value as the cell that contains the date. Define Format_text as “ddd.” See the screenshot below that shows the abbreviated day in cell E3.

Next, let’s extract the day and present it as the full name of the day. Define Value as the cell that contains the date. Define Format_text as “dddd.” See the screenshot below that shows the full name of the day in cell G3.

You can also use the TEXT function to pull the number of the day, as we did with the function DAY. To do this, define Format_text as “dd.” See the screenshot below that shows the number of the day in cell I3.

See the screenshot below for the results of these formulas.

Extract month information

You also have various options to extract and present information about the month. We will start with the MONTH function. The syntax for MONTH is MONTH(serial_number), where serial_number is the cell that contains the date. You can see the formula in cell C3 of the screenshot below.

You can also use the TEXT function to extract month information from a date. For example, to extract and present the three-letter abbreviation for the month, define Value as the cell that contains the date. Define Format_text as “mmm.” See the screenshot below that shows the abbreviated month in cell E3.

In order to extract the month and present it as the full name of the month, define Value as the cell that contains the date and Format_text as “mmmm.” See the screenshot below that shows the full name of the month in cell G3.

See the screenshot below for the results of these formulas.

You can also extract and display the end of a month by using the EOMONTH function. See this May 2019 CPA Insider article for detailed instructions on this technique.

Extract year information

The easiest way to extract the year from a date is to use the YEAR function. The syntax for YEAR is YEAR(serial_number), where serial_number is the cell that contains the date. See the screenshot below that shows the year in cell C3.

You can view a video demonstration of the various ways to extract and present date information as used in this article at the bottom of this page.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.