# Date question

#### jcm996

##### New Member
Is it possible to put a date in with out using the //, I would like to be able to just type 012204 and have the date automatically appear as 01/22/2004 or 1/22/04. But excel keeps seeing it as 5/33/04 if I use date formatting. I understand that is 12204 days forom 01/01/1900, but can you make it stop or is there a way to enter all the dates as 012204 and run a formula to get 01/22/04?

Thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### tactps

##### Well-known Member
The easy way (no VBA):
1. The cell (say A1 in my example) that you want to input the number must be formatted as text, and contain 6 digits when input (as you say 012204)
2. In another cell, type the following formula:
=DATE(100+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))
[Bear in mind that I am from Australia, so I hope I have reversed the month and day correctly for you]

Note - This assume that all dates are after 01/01/2000. If you have dates pre this, you will need to alter the formula to differentiate the old dates.

#### Brian from Maui

##### MrExcel MVP
jcm996 said:
Is it possible to put a date in with out using the //, I would like to be able to just type 012204 and have the date automatically appear as 01/22/2004 or 1/22/04. But excel keeps seeing it as 5/33/04 if I use date formatting. I understand that is 12204 days forom 01/01/1900, but can you make it stop or is there a way to enter all the dates as 012204 and run a formula to get 01/22/04?

Thanks

Would you settle for 01-22-04 instead? Custom format to 00-00-00. downside, you can't use this format for any date calculations.

#### GS2000

##### New Member
This will change the dates as you enter them and allow them to be used in other cell formule as dates! May be a bit complex if your not sure of VBA, if so someone/I can expand

set the format of the col to be used to TEXT first!

An event handler is a small bit of macro code that gets executed every time that a certain event

happens. In this case, we want the macro to run whenever you change a cell. To set up an event handler, follow these steps:

An event handler is associated with just a single worksheet. Start from that worksheet and open the VB editor.
In the upper left window (Project - VBA Project) double click the name of your worksheet.

Enter the following lines of code:

Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
ThisRow = Target.Row
If ThisColumn = 1 And ThisRow > 1 Then ' avoids row 1 (headers!) and assumes date is in col 1
UserInput = Target.Value
If UserInput > 1 Then ' ie a long number is entered
NewInput = Format(Left(UserInput, 2) & "/" & Mid(UserInput, 3, 2) & "/" & Right(UserInput, 2), "short date")
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

Any time a cell is changed in col 1,row 2 onwards the cell that was changed is passed to this program in the variable called "Target". When someone enters a date as 012204, it will evaluate to a number less than one. The If block makes sure to only change cells if they are greater than one. I use the left() and right() mid() functions to break the user input into month year day and insert a "/" in between.

Whenever the user enters "012204", the program will change this entry to "short date". check out formating if you want a different style and change this!, As you are in the US I hope this produces the right result if not post back.

If you want to limit the program to only work on certain cols and rows , you can check the value of Target.Column etc and only execute the code block if you are in the first "n" columns: again if usure post back...

If you ever want to make changes to the worksheet areas being effected (for example is you need to add formulas or change headings, etc.) you can turn off the event handler with this short macro:

Sub TurnEventHanderOff()
Application.EnableEvents = False
End Sub

You can turn event handlers back on with this macro:
Sub TurnEventHanderOff()
Application.EnableEvents = True
End Sub

If you take this concept and change it, there is an important concept to be aware of. When the event handler macro assigns a new value to the cell referenced by Target, Excel counts this as a worksheet change. If you do not briefly turn of event handlers, then Excel will recursively start calling the event handler and you get unexpected results. Before making a change to a worksheet in a change event

handler, be sure to temporarily suspend event handling with the Application.EnableEvents line.

#### Yogi Anand

##### MrExcel MVP

Hi JCM:

Here is a formula based approach ...
y040122h1a.xls
ABCD
1
201220401/22/04
304150404/15/04
Sheet6

In this one you make your six digit entry (formated as text in column A, and in the corresponding row in column B, you get the related date.

Beacause of your project constraints, and/or personal preferences, this method may or may not work for you.

#### Yogi Anand

##### MrExcel MVP
Brian from Maui said:
....
Would you settle for 01-22-04 instead? Custom format to 00-00-00. downside, you can't use this format for any date calculations.
As long as the OP does not have to use it as true date, we can Custom Format it as 01/22/04 using ...

Custom Number Format ... 00\/00\/00

#### Zack Barresse

##### MrExcel MVP

i've used this also Yogi. but like mentioned, cannot use it as a "real" date. i think for flexibility's sake, GS2000 has the right idea here.
although i haven't tried his suggestion personally, i like the idea for a user friendly solution.

#### Yogi Anand

##### MrExcel MVP
I agree -- if the entry has to be used as a true date then the Worksheet_Change event idea by GS2000 is the way to go!

#### tactps

##### Well-known Member
Firstly, GS2000... this is fantastic. I am fairly new to VBA and the event handler will certainly come in handy!

Secondly, the code that you have written, if I'm reading correctly, will only work if the person does not input it as a date to begin with.

How would you alter the statement to allow the user to input either "010104" or "01/01/04"?

#### jcm996

##### New Member
Thanks for the help, I just need them in a 01-02-04 format and they won't be used for any calculations.

Thanks for the VBA code, but I have no idea how to get that to work, Is there a good thread or website for how to use vba code?

Thanks Again

Replies
2
Views
303
Replies
0
Views
258
Replies
3
Views
182
Replies
3
Views
143
Replies
4
Views
193

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.

1,152,816
Messages
5,772,453
Members
425,760
Latest member
paphon

### 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.

### Which adblocker are you using?

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

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