Date Recognition

[Dukester]

Is there a way to make a cell turn a number, say "12", into the day of a specified month, then format according to the date style you want, say "3/12/02"?

[Aladin Akyurek]

On 2002-05-26 11:34, Dukester wrote:
Is there a way to make a cell turn a number, say "12", into the day of a specified month, then format according to the date style you want, say "3/12/02"?

Not sure whether you mean what follows:

If A1 houses 12, A2 4, and A3 2002, then

=DATE(A3,A2,A1) would give you a date you can format as mm/dd/yy.

Is this what you're asking for?

[Dukester]

Not exactly. I have rows for each month of the year. I have to be able to only type in the day, and then have that cell recognize which month it is in and produce "4/12" or whatever. A related problem I have is that it defaults to 1900.

[Aladin Akyurek]

On 2002-05-26 12:00, Dukester wrote:
Not exactly. I have rows for each month of the year. I have to be able to only type in the day, and then have that cell recognize which month it is in and produce "4/12" or whatever. A related problem I have is that it defaults to 1900.

Give an example with the cell references involved.

[Dukester]

Let's say row 5 is for January with D5:R5 alloted for dates to be determined. They refer to no other cells. I need to be able type in any day and have it automatically become, "1/12", etc. The problem is solved if I just type in 1/12, but that is not acceptable for this spreadsheet. Am I truely limited to the formatting options in the pull-down menu, or is there a way to customize them?

[Aladin Akyurek]

On 2002-05-26 12:50, Dukester wrote:
Let's say row 5 is for January with D5:R5 alloted for dates to be determined. They refer to no other cells. I need to be able type in any day and have it automatically become, "1/12", etc. The problem is solved if I just type in 1/12, but that is not acceptable for this spreadsheet. Am I truely limited to the formatting options in the pull-down menu, or is there a way to customize them?

You can't just type 12 in D5 which then magically becomes 1/12. Formatting will not help either, also because the value in D5 will stay the value that you entered.

Maybe VBA programmers would offer code that meets your demand.

Aladin

[Dukester]

I appreciate your help.

[DeLaMartre]

Dukester,
You could make your own "Custom" number format by going to Format/Cells/Custom and typing in "1/"## for the new format. Then, whenever you type in a "4" for instance, you would get "1/4".

You would have to format each column, of course, according to the number of the month.

This is not an "all-encompassing" solution, but it is relatively simple (no VBA).

I hope this helps some.

-Bob in California

[Aladin Akyurek]

On 2002-05-26 13:21, DeLaMartre wrote:
Dukester,
You could make your own "Custom" number format by going to Format/Cells/Custom and typing in "1/"## for the new format. Then, whenever you type in a "4" for instance, you would get "1/4".

You would have to format each column, of course, according to the number of the month.

This is not an "all-encompassing" solution, but it is relatively simple (no VBA).

I hope this helps some.

-Bob in California

Bob,

You can't compute from the cell so formatted that it's 4/12. It will be just 12.

Aladin

[dimrod]

The following code is based on having the year in A1 and numbers 1 to 12 in A5:A16 to represent the months.
This is just for illustration - the code would need revision based on your sheet's actual layout.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [D5:R16]) Is Nothing Then
Application.EnableEvents = False
Target.Value = DateSerial([A1], Cells(Target.Row, 1), Target)
Application.EnableEvents = True
End If
End Sub

Edit : Forgot to mention - if you want to use this method, the code will need some error handlers.


[ This Message was edited by: dimrod on 2002-05-26 13:46 ]