Date formatting

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
Please help me to format a date. Ideally, I would like to type in a six digit number, 093002, for instance, and have Excel format it as 09/30/02 as a date.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Parra

Well-known Member
Joined
Feb 21, 2002
Messages
752
Go to Format > Cells, under the number tab option choose Date, then to the right of it, choose the appropriate, style you want.
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
On 2002-09-30 18:48, Parra wrote:
Go to Format > Cells, under the number tab option choose Date, then to the right of it, choose the appropriate, style you want.

Thanks. I'm looking for something that isn't listed there. I want to type in the numbers without the slashes and have the system insert the slashes automatically. Any help is greatly appreciated.
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi DC,

I don't believe there is a way to do this short of using a VBA macro because the 6-digit form you propose using is not a standard date form. If you would be willing to place delimiters between day, month and year there would be no problem.

If you are willing to entertain such a solution here is one. The following worksheet event macro will convert any date entered in the form you describe in column A of the subject worksheet into the corresponding date.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim year As Integer
Dim month As Integer
Dim day As Integer
Application.EnableEvents = False
Target.NumberFormat = "000000"
year = CInt(Right(Target, 2))
day = CInt(Left(Right(Target, 4), 2))
month = CInt(Left(Target, Len(Target) - 4))
Target = DateSerial(year, month, day)
Target.NumberFormat = "mm/dd/yy"
Application.EnableEvents = True
End If
End Sub

To install this code simply right-click on the worksheet's tab, select View Code, and paste the code into the worksheet's event code pane.
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top