MrExcel.com - Your One Stop for Excel Tips & Solutions

date/serial numbers-help please

Posted by donna on January 03, 2001 7:29 PM

Hello,

I have been trying to enter a date; however, excel keeps entering it as the serial number. I have been searching for quite some time on how to enter an invoice date - with no luck.

Does excel always recognize a date as a serial number.

I went under format, customize (I wanted to use the format: dd,mm,yyy). For example, I only want to enter 010301. Then I want excel to format it to: 01/03/2001. Do I have to enter the slashes?

ANY help would be greatly appreciated.

Thank You.

ibdonna@hazleton.net

Posted by Dave on January 04, 2001 2:10 AM

Hi donna

In short yes you do have to enter the slashes! and Yes Excel does see dates as serial numbers. The date seperators that you could use can are within your PC's Regional Settings.

Start>Settings>Control Panel>Regional Settings/Dates

OzGrid Business Applications

Posted by Joe B on February 17, 2005 3:24 PM

If you use some formulas, this can be done. Say that in cell A2, you enter 11272004. Use the following formulas:

  • Cell B2 contains the month with =(INT(A2/1000000))
  • Cell C2 contains the Day with =(INT(A2/10000))-(100*B2)
  • Cell D2 contains the Year with =A2-(10000*INT(A2/10000))
  • Cell E2 contains the result with =B2&"/"&C2&"/"&D2

Posted by Bill on March 5, 2005 8:14 AM

Joe's post above creates text in E2 that looks like a formula. I would replace this formula with =DATE(D2,B2,C2).

Also, note that you could use a worksheet event macro in VBA to convert A2 to E2. It would be along the lines of the http://www.mrexcel.com/tip029.shtml.