Creating a Custom Date Format

[poorwallace]

Hello,

I'm not very familiar with creating different formats, so I'm looking for a bit of assistance here. I want to be able to type in 11.2 for Nov 2 and have the result be 11/2/2005...So far I haven't had much luck, though I've tried a bunch of variations. Any help would be greatly appreciated.

Thanks
Wally

 

[Andrew Poulsom]

If you format the cell in advance as mm/dd/yyyy you can enter the date as 11/02 or 11-02 (or 11/2 or 11-2).

 

[poorwallace]

Andrew,

I had tried that and the problem was that when I typed 11.1 (for Nov 1), I was getting

1/11/1900 2:24:00 AM

When I hit enter. So is there a way that I can type 11.1 and get 11/1/2005? I think I need to create a format, but I'm a bit unsure about where to start on this one.

Thanks
Wally

 

[Seti]

The problem you are running into is that you are not inputting the date you think you are. 11.1 is the 11th day since 1/1/1900. the .1 is 1/10th of a day. If you must input the dates this way, you will need VBA code to trap the entry and change it to the date you intended. Why do you need to input the date this way?

 

[Andrew Poulsom]

Andrew,

I had tried that and the problem was that when I typed 11.1 (for Nov 1), I was getting

1/11/1900 2:24:00 AM

When I hit enter. So is there a way that I can type 11.1 and get 11/1/2005? I think I need to create a format, but I'm a bit unsure about where to start on this one.

Thanks
Wally

I didn't tll you to type 11.1 (which won't work). I told you to type 11-1 or 11/1.

 

[poorwallace]

Thanks for the information...I'll play around with coming up with some VB code to address this situation. I was just hoping that I could address it through formatting....Andrew--what you told me did work correctly (and I had tried that), but the dates are being entered in the 11.1 format...Oh well.

Thanks for all your help!
Wally

 

[Seti]

Here is some code to do what you are asking. The only caveat is that 11/1/2005 would need to be 11.01 rather than 11.1 which my code would treat as 11/10/2005. All dates use the current year and the active range to check for is A1:A20. Change to suit. This goes in the sheet code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myMonth As Long, myDay As Long
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
    myMonth = Int(Target)
    myDay = 100 * (Target - myMonth)
    Target = DateSerial(Year(Date), myMonth, myDay)
    Target.NumberFormat = "mm/dd/yyyy"
End If
Application.EnableEvents = True
End Sub