Month-date

L

lynne

Is there a way to enter just numeric without having to
use the hyphen when you what to enter a date in excel?
So, instead of entering 08-01 to get 08/01/2003.
Can I enter 080103 and get 08-01-2003?

Thanks,
Lynn
 
J

JMay

sure, on appropriate cells Format, Cells, Number-Tab, Custom and in Box
enter:
00-00-0000 << Will display 08-01-2003 after you enter 08012003; but
keep in
mind the content of the cell will be the number 8012003.
HTH
 
R

Ron Rosenfeld

Is there a way to enter just numeric without having to
use the hyphen when you what to enter a date in excel?
So, instead of entering 08-01 to get 08/01/2003.
Can I enter 080103 and get 08-01-2003?

Thanks,
Lynn
Click to expand...

If you don't mind entering the numeric in one cell and converting it to
another, then, if the cell where you enter the numeric is named 'dt',:

=DATE(MOD(dt,100)+1900+100*(MOD(dt,100)<30),
INT(dt/10^4),
MOD(INT(dt/100),100))

will convert it (with years 00-29 being interpreted as 2000-2029).

If you need to convert it in place, you can use the same algorithm in a VBA
macro, probably an event driven macro, like this:

================
Sub DateEntry()
Dim c As Range
Dim Yr As Integer, Mn As Integer, Dy As Integer

For Each c In Selection
If Not IsDate(c) And c >= 10100 And c <= 123199 Then
Yr = c Mod 100 + 1900 - 100 * ((c Mod 100) < 30)
Mn = Int(c / 10 ^ 4)
Dy = Int(c / 100) Mod 100
c.Value = DateSerial(Yr, Mn, Dy)
End If
Next c
End Sub
=================


--ron
 
L

lynne

Thanks, I'll try this.
-----Original Message-----
sure, on appropriate cells Format, Cells, Number-Tab, Custom and in Box
enter:
00-00-0000 << Will display 08-01-2003 after you enter 08012003; but
keep in
mind the content of the cell will be the number 8012003.
HTH





.
Click to expand...
 
L

lynne

Thanks, I'll try this.
-----Original Message-----
This will work as a date. Test with =a2+2 in col b.

right click on sheet tab>view code>insert this>save
Remember to FIRST format the column as TEXT and
always enter 6 numbers. 080103

this is set up to work below row 1 and only in col A.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True 'fixes if a problem
Application.EnableEvents = False
If Target.Row > 1 And Target.Column = 1 Then
Target = Left(Target, 2) & "-" & Mid(Target, 3, 2) & "-" & Right(Target,
2)
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX
[email protected]



.
Click to expand...
 
Top