Date question

jcm996

New Member
Joined
Sep 6, 2003
Messages
23
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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.
:biggrin:
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Dec 26, 2002
Messages
20
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.

GS (adapted from other ideas) :)
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Jan 20, 2004
Messages
3,459
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
Joined
Sep 6, 2003
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,020
Members
410,646
Latest member
LegenDSlayeR
Top