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? |
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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 06:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com