# Thread: Excel - Date Format (2000 (9.0.2720))

1. ## Excel - Date Format (2000 (9.0.2720))

How do I format a date so that when I enter 010102 I get 01/01/02 instead of 08/29/27?

2. ## Re: Excel - Date Format (2000 (9.0.2720))

enter 01/01/02 formatted as "mm/dd/yy" will do it.

Excel will autoconvert and calc a serialdate. by entering in the NUMBER 10,102 excel does NO conversion since it is already a number and gives you the date that is 10,102 days past Jan 1, 1900.

If you want to convert, you will have to use a function or to automatically do it, create a macro.

if the date is in A1 this function will calculate the serial date in another cell.
=DATE(A1-INT(A1/10000)*10000-INT((A1-INT(A1/10000)*10000)/100)*100+2000,INT(A1/10000),INT((A1-INT(A1/10000)*10000)/100))

This assumes ALL years are in 2000.

If you want an automacro, the following will convert anything in Col A. This assumes <30 are 2000 and >= 30 are 1900 (like XL97)
<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Dim rCell As Range
Dim lYear As Long
Dim lMonth As Long
Dim lDay As Long
Application.EnableEvents = False
For Each rCell In Intersect(Target, Range("A:A"))
lMonth = Int(rCell.Value / 10000)
lDay = Int(rCell.Value - lMonth * 10000) / 100
lYear = rCell.Value - lMonth * 10000 - lDay * 100
If lYear < 30 Then
lYear = lYear + 2000
Else
lYear = lYear + 1900
End If
rCell.Value = DateSerial(lYear, lMonth, lDay)
Next rCell
Application.EnableEvents = True
End If
End Sub</pre>

Steve

3. ## Re: Excel - Date Format (2000 (9.0.2720))

(Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

Hi steve and bbrown5,

Chip Pearson has a similar macro to the one Steve has supplied at: http://www.cpearson.com/excel/DateTimeEntry.htm

I have used a similar macro in a workbook for several years and found it most effective, but I did expand it to permit the normal format data entry as well as the quick entry.

Good Luck

Peter Moran
Two heads are always better than one!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•