Format cells for date & slashes
Posted by GeeCee on January 09, 2001 7:07 PM
Is it possible in Excel 2000 to format a column(group of cells) with the date format (mm/dd/yy) and NOT have to type the slashes yet have the slashes show when you type 010201 & you want it to look like this: 01/02/01?
Posted by Mark W. on January 09, 2001 7:47 PM
GeeCee,consider the following:
1. Before entry format your cells as Text.
2. Enter your dates in 'mmddyy' form.
3. Select the cells where your data was entered
4. Choose the Data Text to Columns... menu command.
5. At the Text Wizard's step 3 of 3 choose the
Date format (MDY).
Posted by Dave on January 09, 2001 9:29 PM
Hi GeeCee
It is possible by highlighting your column(s) then going to Format>Cells>Numbers>Custom and using any one of the pre-defined ones as a start type: ##/##/## then click OK.
OzGrid Business Applications
Posted by Dave Hawley on January 09, 2001 9:33 PM
Hmmm, that is a bit hard to read it should be:
## / ## / ## without any spaces. I should also mention that excel wont see this as a date by simply the number 010201
OzGrid Business Applications
Posted by Tim Francis-Wright on January 10, 2001 7:17 AM
If the workbook needs the dates to be dates
for calculation purposes, a worksheet
change sub might do(in the code for the sheet in question).
If the cells that need the formatting are defined
as a range called Dates, the following code will
work:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Dim cel As Range, Sect As Range
Set Sect = Intersect(Target, Range("Dates"))
Application.ScreenUpdating = False
If Not (Sect Is Nothing) Then
Application.EnableEvents = False
For Each cel In Sect
If (cel.Value > 10000 and cel.Value <1000000 Then
cel.NumberFormat = "General"
cel.Value = DateValue(Mid(cel.Value, 1, Len(cel.Value) - 4) & "/" & Mid(cel.Value, Len(cel.Value) - 3, 2) & "/" & Right(cel.Value, 2))
cel.NumberFormat = "mm/dd/yy"
End If
Next
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub
Hope this helps.
