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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
 
Upvote 0
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.
 
Upvote 0
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) :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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