Date Format

bkeeper

New Member
Joined
Oct 12, 2005
Messages
21
I am stumped on date formatting.

I have a column that requires the user to input the date. They can type in 123105, 12312005, 12/31/05, etc....pretty much any format they want.

If they type with the / or - between each mmddyy, I have no problem. But when they type in only the numbers, Excel sees them as the serial number and converts the entry to the serial number data. For example 123105 would be 1/17/2237. I want to be able to show it as 12/31/05.

I've formatted the column to date mm/dd/yy and use data validation of date greater than 1/1/1900.

Thanks for any help.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
670
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi Bkeeper,

Have you tried the link below ?? Go to the bottom of the page &
you get the option to download.

It offers a pop up calender by way of selecting cell >
right click > option insert date.

http://www.fontstuff.com/vba/vbatut07.htm

It might save you some time keying data /( Dates )
:cool: Hope this helps !!

Russ
 

bkeeper

New Member
Joined
Oct 12, 2005
Messages
21
Russ At Home said:
Hi Bkeeper,

Have you tried the link below ?? Go to the bottom of the page &
you get the option to download.

It offers a pop up calender by way of selecting cell >
right click > option insert date.

http://www.fontstuff.com/vba/vbatut07.htm

It might save you some time keying data /( Dates )
:cool: Hope this helps !!

Russ

Russ,

That is a great idea, but I have hundreds of row of data that need to be keyed in, so using a calander lookup would be more troublesome than typing in the date.

I am trying to make it so when the users type in the date, it will format it to the mm/dd/yy, instead of looking at it as a serial date number and translating it to a date.

Thanks.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,438
Office Version
  1. 365
Platform
  1. Windows
bkeeper,

Let's say that the person will be entering dates into column B. Attached is a macro that will automatically convert what they have entered into a valid date (as long as they use some sort of valid date entry).

In order for this to work, first format your entire column as Text (this is important so that it does not drop leading zeroes and the macro can perfrom all the necessary calculations).

Right click on the Sheet tab name, select View Code, and paste this code in the resulting window:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myDateCheck As Long

'   Change all entries into column B to dates
    If Target.Column = 2 Then
        Application.EnableEvents = False
        On Error GoTo err_chk
        myDateCheck = Application.WorksheetFunction.Search("/", Target, 1)
        On Error GoTo 0
        If myDateCheck > 1 Then
            Target.NumberFormat = "mm/dd/yy"
            Target = Target.Value
        Else
            Select Case Len(Target)
                Case 6
                    Target.NumberFormat = "mm/dd/yy"
                    Target = DateSerial(Right(Target, 2), Left(Target, 2), Mid(Target, 3, 2))
                Case 8
                    Target.NumberFormat = "mm/dd/yy"
                    Target = DateSerial(Right(Target, 4), Left(Target, 2), Mid(Target, 3, 2))
                Case Else
                    MsgBox "You have not entered a valid date"
                    Target = ""
            End Select
        End If
        Application.EnableEvents = True
    End If
       
    Exit Sub

err_chk:
    myDateCheck = 0
    Resume Next
        
End Sub
Now whenever an entry is made into column B, it should automatically convert it to the date intended.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top