Value and Text Function│Date Value Function│Data 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:
=VALUE(A2)
The text function to convert a date to a serial number:
=TEXT(A2,"0")
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.
=DATEVALUE("YYYY-MM-DD")
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.
=DATEVALUE(YEAR(A2)&"-"&MONTH(A2)&"-"&DAY(A2))
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".