Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dates - Have Cake, Eat too
I have a specific need to show 'month and year' and 'day, month and year' in
the same column. Because of multiple user input, I would like to make this as simple as possible. If they enter 1/05, I want to see January, 2005 and if the enter 1/10/05, I want to see January 10, 2005. From what I can see, there is no way to format the entire column and acheive the stated goal. One must choose one format and customize/reformat individual cells, which defeats the purpose. Any magic out there? |
#2
|
|||
|
|||
Maybe do this.........
Format column A as TEXT Go ahead and enter 1/10, 1/10/05, etc etc down the column In helper column B put this in B1 and copy down......... =IF(LEN(A1)=6,TEXT(A1,"mm/dd/yyyy"),TEXT(A1,"mm/dd")) Vaya con Dios, Chuck, CABGx3 "Manta.ray" wrote in message ... I have a specific need to show 'month and year' and 'day, month and year' in the same column. Because of multiple user input, I would like to make this as simple as possible. If they enter 1/05, I want to see January, 2005 and if the enter 1/10/05, I want to see January 10, 2005. From what I can see, there is no way to format the entire column and acheive the stated goal. One must choose one format and customize/reformat individual cells, which defeats the purpose. Any magic out there? |
#3
|
|||
|
|||
You would need an event macro that returns a date, by only entering m/dd it
will default to month = 1, day = 5 and current year so every time you enter let's say 04/05 it will return (assuming you do it today) 04/05/05 but if you put in 04/04 for Apr 2004 it will in fact return 04/04/05 so you can find a macro here that will add date delimiters http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom "Manta.ray" wrote in message ... I have a specific need to show 'month and year' and 'day, month and year' in the same column. Because of multiple user input, I would like to make this as simple as possible. If they enter 1/05, I want to see January, 2005 and if the enter 1/10/05, I want to see January 10, 2005. From what I can see, there is no way to format the entire column and acheive the stated goal. One must choose one format and customize/reformat individual cells, which defeats the purpose. Any magic out there? |
#4
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then If Len(.Value) - Len(Replace(.Value, "/", "")) = 1 Then .NumberFormat = "mmmm, yyyy" ElseIf Len(.Value) - Len(Replace(.Value, "/", "")) = 2 Then .NumberFormat = "d mmmm, yyyy" Else .NumberFormat = "@" End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Manta.ray" wrote in message ... I have a specific need to show 'month and year' and 'day, month and year' in the same column. Because of multiple user input, I would like to make this as simple as possible. If they enter 1/05, I want to see January, 2005 and if the enter 1/10/05, I want to see January 10, 2005. From what I can see, there is no way to format the entire column and acheive the stated goal. One must choose one format and customize/reformat individual cells, which defeats the purpose. Any magic out there? |
#5
|
|||
|
|||
Sorry, I'm just learning to read, you see........<g
This one is formatted more like what you asked for......... =IF(LEN(A1)=6,TEXT(A1,"mmmm dd, yyyy"),TEXT(A1," mmmm, yyyy")) Vaya con Dios, Chuck, CABGx3 "CLR" wrote in message ... Maybe do this......... Format column A as TEXT Go ahead and enter 1/10, 1/10/05, etc etc down the column In helper column B put this in B1 and copy down......... =IF(LEN(A1)=6,TEXT(A1,"mm/dd/yyyy"),TEXT(A1,"mm/dd")) Vaya con Dios, Chuck, CABGx3 "Manta.ray" wrote in message ... I have a specific need to show 'month and year' and 'day, month and year' in the same column. Because of multiple user input, I would like to make this as simple as possible. If they enter 1/05, I want to see January, 2005 and if the enter 1/10/05, I want to see January 10, 2005. From what I can see, there is no way to format the entire column and acheive the stated goal. One must choose one format and customize/reformat individual cells, which defeats the purpose. Any magic out there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Plotting Dates | Excel Worksheet Functions | |||
How do I get Excell to sort dates that range from 1800 to 1900's | New Users to Excel | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |