Tag Archives: text to numbers

DATEVALUE Function

Excel has a number of date functions.  Unfortunately, they don’t all work if Excel treats the date as text.  When working with downloaded data, sometimes the date must be converted from text to a format recognized by Excel.

One method is to use the DATEVALUE function.  This function converts a date in text format to a number that represents the date in Excel date-time code.
Open the exercise workbook and select the Datevalue worksheet.

In cell A1, is the text ‘12/31/11.  In cell B1, type =DATEVALUE(A1)
datevalue 1

The value in cell B1 is the Excel date-time code.  However, it requires some formatting to make sense.
Format cell B1 as a date either by using the Home menu and Number selection or the Home menu Format Cells selection.

datevalue2Format B1 in the date format of xx/xx/xxxx.datevalue3

You can tell that the date in cell A1 is in text format due to its left justification in the cell while the date in B1 is a date by its right justification in the cell.

This is an excerpt from our Must Know Functions for CPAs.

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom