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
 
for tactps

Improved, now allows format ###### or ##/##/## or##/##/#### and still allows use as real dates in other calcs. (y)

for jcm996, it's not too hard to get this code into excel do this.
1. copy the code below
2. right click on the worksheet tab you want it to work on
3. from the menu select "VIEWCODE"
4. paste the code into the VBA screen which appears
5. close the VBA screen
6 That should be it

notes - :eek:
A - this is set up to work in col 1 (A) from row 2 down. that may not suit depending on where your dates are.
B - if you need to turn it of set up a button to run the on off subs, if you cant do this then press [ALT-F8] and run the macro needed
c - tested on UK date system so ??, and tested on Excel2000, so results mary vary.
D - if you prefer "-" to "/" then just change it in the code



Code:
' copy code from below here
'this is the event handler

Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
ThisRow = Target.Row
Dateformat1 = "######"
Dateformat2 = "##[-/]##[/-]##"
DateFormat3 = "##[-/]##[/-]####"
' chage the col and rows below  to where date is used
' currently set to avoid row 1 (headers!) and assumes date is in col 1
If ThisColumn = 1 And ThisRow > 1 Then 
    userinput = Target.Value
        
        
        If userinput Like Dateformat1 Then
        NewInput = Format(Left(userinput, 2) & "/" & Mid(userinput, 3, 2) & "/" & Right(userinput, 2), "short date")
              
        
        ElseIf userinput Like Dateformat2 Or userinput Like DateFormat3 Then
                
        NewInput = Format(Left(userinput, 2) & "/" & Mid(userinput, 4, 2) & "/" & Right(userinput, 2), "short date")
           
     End If
        
    Application.EnableEvents = False
    Target = NewInput
    Application.EnableEvents = True
End If


End Sub

'You can turn event handlers off with this macro:
Sub TurnEventHanderOff()
Application.EnableEvents = False
End Sub


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

'stop copying code here


if any problems customising this let me know your specifics.. :biggrin:
 
Upvote 0

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.
If you aren't going to be using the data in the cells for any calculations you can just format the cell, using a custom format as follows:

##"-"##"-"##

Thiswill produce what you require, IF, and only if, 6 digits are entered. You can use data validation to ensure this, with appropriate message in the error box that appears when an incorrect entry is made.

I hope this helps.

Regards,

Simsy
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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