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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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