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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Go to Format > Cells, under the number tab option choose Date, then to the right of it, choose the appropriate, style you want.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top