How to Convert a Date into a Serial Number

Value and Text FunctionDate Value FunctionData Formatting

Dates are stored as sequential serial numbers even the display can be very different, for example, 5/18/2011 or May 18, 2011.

To convert a date into a serial number, you can use the Value Function or the Text Function. The value function returns a number while the Text Function returns a text string.

The value function to convert a date to a serial number:


The text function to convert a date to a serial number:


You can also use the DATEVALUE Function, but a bit different because the function requires a text string that represents dates.

You can use the date in the quotation mark which represents a text string or to convert the date into a text string.

1. To Use Quotation Mark: The disadvantage is that it is very hard to use this directly when you have a column of dates.


For example, =DATEVALUE("2011-5-8") returns 40671.

2. Format Dates into Text Strings: Dates are normally stored as serial numbers and we convert them into text strings.


Where A2 is the cell that contains the date.[/fusion_builder_container]

You can also format dates directly into serial numbers with the steps below:

Step 1: Select the date range and click the "Home" tab from the ribbon;

Step 2: Click the right bottom corner in the "Number" group;

Step 3: In the "Format Cell" window, click "Number", change "Decimal places" to 0, uncheck the "Use 1000 Separator" box;

Step 4: Click "OK".

Leave a Reply