Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
One of our databases churns out a 5 digit date such as 10410 is
October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? Thanks for any ideas. Pierre |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
One of our databases churns out a 5 digit date such as 10410 is
October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
=TEXT(DATE(2000+MID(A1,3,1),RIGHT(A1,2),1),"mmm-yy")
"Pierre" wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? Thanks for any ideas. Pierre |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
On Jun 20, 9:33 am, "Rick Rothstein \(MVP - VB\)"
wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick Rick, thanks for the reply, still not behaving though. Using the above formula, 10510 returns Jan-05, instead of Oct-05. . .have played with the fields, still not doing it. Do you know of a custom date format method, instead of an additional cell reference? It would save having to change the fields and resulting data. Thanks for your help. Pierre |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
Try this:
=TEXT(RIGHT(A1,2)&"-1","mmm-")&MID(A1,2,2) "Rick Rothstein (MVP - VB)" wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
How would a date like April 1999 look like?
If all dates you want to convert are after 12/31/1999 you can use =DATE(2000+MID(A1,2,2),RIGHT(A1,2),1) if not post back note that there are no dates in Excel without days so I put the first day of the month there, so 10510 will be October 1st 2005, to just get the year month use custom formatting like mmm-yy -- Regards, Peo Sjoblom "Pierre" wrote in message oups.com... On Jun 20, 9:33 am, "Rick Rothstein \(MVP - VB\)" wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick Rick, thanks for the reply, still not behaving though. Using the above formula, 10510 returns Jan-05, instead of Oct-05. . .have played with the fields, still not doing it. Do you know of a custom date format method, instead of an additional cell reference? It would save having to change the fields and resulting data. Thanks for your help. Pierre |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
On Jun 20, 9:40 am, Teethless mama
wrote: =TEXT(DATE(2000+MID(A1,3,1),RIGHT(A1,2),1),"mmm-yy") "Pierre" wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? Thanks for any ideas. Pierre- Hide quoted text - - Show quoted text - Thanks TM Pierre |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
One of our databases churns out a 5 digit date such as 10410 is
October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick Rick, thanks for the reply, still not behaving though. Using the above formula, 10510 returns Jan-05, instead of Oct-05. . .have played with the fields, still not doing it. Whoops! I copied the wrong "test" formula from my worksheet. Try this formula instead... =TEXT(DATE(100,RIGHT(A8,2),1),"mmm-")&MID(A8,2,2) Do you know of a custom date format method, instead of an additional cell reference? It would save having to change the fields and resulting data. I don't see how... your "date number" needs would need to be manipulated before the Excel could consider it a date that could be formatted. Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
On Jun 20, 10:10 am, "Rick Rothstein \(MVP - VB\)"
wrote: One of our databases churns out a 5 digit date such as 10410 is October 2004. The 1st 3 characters indicate the year (e.g., 107 is 2007, 106 is 2006, etc) and the last 2 characters indicate the month. Is there a custom date format that would make it easier to read, and have the result appear to be: Apr-07, or the like? A1: 10410 B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2) Rick Rick, thanks for the reply, still not behaving though. Using the above formula, 10510 returns Jan-05, instead of Oct-05. . .have played with the fields, still not doing it. Whoops! I copied the wrong "test" formula from my worksheet. Try this formula instead... =TEXT(DATE(100,RIGHT(A8,2),1),"mmm-")&MID(A8,2,2) Do you know of a custom date format method, instead of an additional cell reference? It would save having to change the fields and resulting data. I don't see how... your "date number" needs would need to be manipulated before the Excel could consider it a date that could be formatted. Rick- Hide quoted text - - Show quoted text - Rick, the sql data which gets extracted appears in certain cells, and I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. Thanks for the 2nd pass on your formula, b t w. Regards. Pierre |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
Rick, the sql data which gets extracted appears in certain cells, and
I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. I have a possible idea for a macro function which could toggle the display back and forth between you number format and the month-year format, but I would need to know what those "certain cells" are. Are they fixed in number (count)? How many of them are there? Are the contiguous? Thanks for the 2nd pass on your formula, b t w. Regards. Yeah, but I would go with either Peo's or Teethless mama's as they are tighter. Rick |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
Rick, the sql data which gets extracted appears in certain cells, and
I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. I have a possible idea for a macro function which could toggle the display back and forth between you number format and the month-year format, but I would need to know what those "certain cells" are. Are they fixed in number (count)? How many of them are there? Are the contiguous? I should point out that the above idea will not be a viable one if you have calculations or dependencies on those cells containing your 1xxxx number. On the other hand, if those cells are for display only, and you have no need to retain the 1xxxx format, we could probably make the conversion automatic (that is, without the need for a toggle). Let me know if either of these conditions apply. Rick |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
On Jun 20, 12:33 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Rick, the sql data which gets extracted appears in certain cells, and I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. I have a possible idea for a macro function which could toggle the display back and forth between you number format and the month-year format, but I would need to know what those "certain cells" are. Are they fixed in number (count)? How many of them are there? Are the contiguous? Thanks for the 2nd pass on your formula, b t w. Regards. Yeah, but I would go with either Peo's or Teethless mama's as they are tighter. Rick Rick, the number of fields changes. They begin at E2, and go as far right as the extracted data gets. The 10408, is a pivot table extracted field(E2, and beyond where applicable). Thanks. Pierre |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
Rick, the sql data which gets extracted appears in certain cells, and
I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. I have a possible idea for a macro function which could toggle the display back and forth between you number format and the month-year format, but I would need to know what those "certain cells" are. Are they fixed in number (count)? How many of them are there? Are the contiguous? Thanks for the 2nd pass on your formula, b t w. Regards. Yeah, but I would go with either Peo's or Teethless mama's as they are tighter. Rick Rick, the number of fields changes. They begin at E2, and go as far right as the extracted data gets. The 10408, is a pivot table extracted field(E2, and beyond where applicable). We can work out the activation method later... for now, I just want to see if this treatment is OK with you. Start a new workbook and put some of your "date numbers" in cells E2, E3, E4, etc. for maybe 5 or 6 cells. Now, right click the sheet's tab and select View Code from the popup window. Paste the code below into the code window that appears. Now, return to your worksheet and double click anywhere on the sheet and watch the cells you entered your "date numbers" in. Double click the sheet again, and again, etc. If all went right, you should be seeing the dates toggle back and forth between you "date number" and the "friendly format" you asked for. Rick Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) Dim X As Long Dim LastColumn As Long Dim TempDate As Date Dim DateCell As Range Const DateRow As Long = 2 Const StartColumn As Long = 5 LastColumn = Cells(DateRow, 255).End(xlToLeft).Column For X = StartColumn To LastColumn Set DateCell = Cells(DateRow, X) DateCell.NumberFormat = "@" If DateCell.Text Like "1####" Then TempDate = DateSerial(Mid$(DateCell.Text, 2, 2), _ Right$(DateCell.Text, 2), 1) DateCell.NumberFormat = "mmm-yy" DateCell.Value = TempDate Else DateCell.NumberFormat = "00000" DateCell.Value = Format(DateCell.Value, "1yymm") End If Next Cancel = True End Sub |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
On Jun 20, 1:45 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Rick, the sql data which gets extracted appears in certain cells, and I'd like to leave those cells as the data display sensitive cells, without adding another row or column to extract the date-text-date format. I have a possible idea for a macro function which could toggle the display back and forth between you number format and the month-year format, but I would need to know what those "certain cells" are. Are they fixed in number (count)? How many of them are there? Are the contiguous? Thanks for the 2nd pass on your formula, b t w. Regards. Yeah, but I would go with either Peo's or Teethless mama's as they are tighter. Rick Rick, the number of fields changes. They begin at E2, and go as far right as the extracted data gets. The 10408, is a pivot table extracted field(E2, and beyond where applicable). We can work out the activation method later... for now, I just want to see if this treatment is OK with you. Start a new workbook and put some of your "date numbers" in cells E2, E3, E4, etc. for maybe 5 or 6 cells. Now, right click the sheet's tab and select View Code from the popup window. Paste the code below into the code window that appears. Now, return to your worksheet and double click anywhere on the sheet and watch the cells you entered your "date numbers" in. Double click the sheet again, and again, etc. If all went right, you should be seeing the dates toggle back and forth between you "date number" and the "friendly format" you asked for. Rick Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Range, Cancel As Boolean) Dim X As Long Dim LastColumn As Long Dim TempDate As Date Dim DateCell As Range Const DateRow As Long = 2 Const StartColumn As Long = 5 LastColumn = Cells(DateRow, 255).End(xlToLeft).Column For X = StartColumn To LastColumn Set DateCell = Cells(DateRow, X) DateCell.NumberFormat = "@" If DateCell.Text Like "1####" Then TempDate = DateSerial(Mid$(DateCell.Text, 2, 2), _ Right$(DateCell.Text, 2), 1) DateCell.NumberFormat = "mmm-yy" DateCell.Value = TempDate Else DateCell.NumberFormat = "00000" DateCell.Value = Format(DateCell.Value, "1yymm") End If Next Cancel = True End Sub- Hide quoted text - - Show quoted text - DANG!!! Rick, it works like a charm. Now, where do want those pizza's sent? Thanks so much. Pierre |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
DANG!!!
So, I am guessing you liked the concept then? <g Rick, it works like a charm. Now, where do want those pizza's sent? Glad you liked it. How did you know pizza's are one of my favorite foods? Thanks so much. You are quite welcome. I just want to remind you, though, of the caveat I mentioned earlier... I should point out that the above idea will not be a viable one if you have calculations or dependencies on those cells containing your 1xxxx number. ....and the reason for this caveat is because when you toggle to the "friendly format", the physical contents of the cell are changed... it is not simply a "cosmetic" formatting of the content's display like Cell/Formatting does. Oh, and I am guessing you like the double-click activation method... I was originally thinking of a CommandButton or a CheckBox as the activation method, but chose double-clicking as it was easier to get you to implement in an example. Rick |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for custom date format
You are quite welcome. I just want to remind you, though, of the caveat I
mentioned earlier... And one other caveat I didn't mention before... using the double-click method for activation takes away your ability to double-click a cell and edit it in place, meaning any cell editing will have to be done in the formula bar (you can, of course, still type a replacement value directly into a cell as that does not require double-clicking to do). Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Date & Time format | Excel Worksheet Functions | |||
Date Format in Custom Header | Excel Discussion (Misc queries) | |||
Custom format date | New Users to Excel | |||
Custom Date format ie. 01.01.05 W1 (W1 is week 1) | Excel Discussion (Misc queries) | |||
How to write a "Text" or date custom format | Excel Worksheet Functions |