When you see a date in a cell in Excel, in the back end it is still stored as a number. In some cases, you may not want to show the date and instead show the serial number it represents. For example, instead of showing 31 Dec 2022 in a cell, you may want to show its numerical value, which is 44926. In this tutorial, I will show you some simple ways you can use to convert a date into a serial number in Excel. I will also show you the method that would work in case you have the dates in the text format.
How To Convert Date To Serial Number In Excel?
As I mentioned, dates are stored as numbers in the back-end in Excel. So if you have a date in a cell and you want to show the serial number instead, all you need to do is change the format so that the serial number is displayed. You can either use the option in the ribbon to quickly convert any date into a serial number, or you can use the custom formatting dialog box that gives you a lot more control. Let me show you both of these methods here.
Using the Format Drop-down in the Ribbon (Quick Method)
Below I have some dates in column A that I want to convert into numbers. Here are the steps to do this: The above steps would instantly convert the format of all the cells from Date to General. A general format is where no specific formatting has been applied, so a number would be shown as a number (and if there are dates in the selected cells, these would be converted back to its serial numbers). Note that this method will only work if the date in the cell is in a format that Excel recognizes as a date. For example, if you have a date as ‘Dec 31 2022′ or ’12 31 2022’, these will not be converted into their serial number, because Excel does not recognize these as a valid date format. Below I have some commonly used valid date formats in Excel.
Using the Format Cells Dialog Box (Gives More Control)
Another great way to show dates as numbers would be by using the Format Cells dialog box. While it also works by changing the cell formatting from a date to a number, when compared with the above method where we used the Format drop-down and chose the General option, this method gives you a little more control. Assuming I have the same data set where I have dates in column a comma below are the steps to convert these dates into serial numbers: Below are the steps to do this using the ‘Format Cells’ dialog box: The above steps would also give you the same result, where the date will now be shown as its serial number. While this may look like a longer way to convert dates to numbers, you can see that you get a lot more control when using the Format Cells dialog box. You can specify whether you need 1000 separators or decimal places. Apart from this, it also shows you the option where you can select if you want to show negative numbers in red (with or without a negative sign). Of course, this is not quite useful as a date can never be negative in Excel. Again, this method is only going to work for dates that are considered a valid date format by Excel. If a cell has a date that’s not recognized as a valid date format, and you use the above method to convert it into a serial number, nothing would happen.
Change Date in Text Format to Serial Number (Using DATEVALUE)
If you have the dates in a proper format, which means that they are numbers in the back end, then you can simply change the cell format to show the serial number instead of the date (using any of the two methods shown above). But in many cases, you may get a column full of dates that are text strings and not numbers in the back end. In my experience, this often happens when you download your data from a database or from the web. It recently happened when I downloaded my bank statement as an Excel file, and the date column had text strings. And many times, people add an apostrophe before the date to make a text string. In such cases, changing the cell format is not going to give you the serial number for that date. But there is a workaround – the DATEVALUE function. The DATEVALUE function is made specifically for this use case, where it takes the date in the text format as the input and gives you the serial number for that date. Below I have a column where I have dates in text format for which I want to get the serial number. And below is the formula that will do this for me: One useful use case of this function is when you have dates in the text format, and you want to convert these into proper date format. While you cannot do that when the dates are in the text format, you can first use the DATEVALUE function to convert it into the corresponding serial number, and then change the formatting of the dates Note that the DATEVALUE function only works with text data. So if you have cells that have dates in proper format (that are stored as numbers in the back end), this formula would give you a value error.
Convert Date to Number in the MMDDYYYY or DDMMYYY Format
In some, not-so-common, situations users may want to convert a date into an 8-digit number in the DDMMYYYY or the MMDDYYY format. For example, 31 Dec 2022 would be 31122022 in the DDMMYYYY format and 12312022 in the MMDDYYYY format. While it may look like a complex problem to solve, the fact that you can specify you’re own custom formatting in Excel makes it an easy task. Below I have some dates in column E that I want to show in the 8-digit MMDDYYYY format. Here are the steps to do this: The above steps would change the format of the selected cells and make sure that any number is shown in the MMDDYYYY format. Note that this would only work if you have numerical values in the cells. In case you have text values, all numbers that have been formatted as text, this wouldn’t work. In case you want to show the dates in the DDMMYYYY format, use ddmmyyyy in step 5. In this tutorial, I showed you how to convert dates into serial numbers in Excel. If you have the dates in the proper format (i.e., these are numbers in the back end), you can get the serial number by changing the cell format using the Format Cells dialog box (or the General option in the Ribbon). In case you have dates that are in the text format, you need to use the DATEVALUE function to convert these dates into the corresponding serial numbers. I also covered how to convert dates into the 8-digit format – MMDDYYYY or DDMMYYYY. I hope you found this Excel tutorial useful. Other Excel tutorials you may also like:
How to Convert Serial Numbers to Dates in ExcelConvert Text to Numbers in ExcelConvert Scientific Notation to Number or Text in ExcelCombine Date and Time in ExcelConvert Time to Decimal Number in Excel (Hours, Minutes, Seconds)Convert Date to Text in Excel
