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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
696
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
63,302
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,986
Members
430,331
Latest member
Syed Yasir Hannan

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