Looking for custom date format

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

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
 
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?
Click to expand...

A1: 10410

B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2)

Rick
 
A1: 10410

B1: =TEXT(RIGHT(A1,2),"mmm-")&MID(A1,2,2)

Rick
Click to expand...

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
 
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
 
One of our databases churns out a 5 digit date such as 10410 is
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.
Click to expand...

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.
Click to expand...

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
 
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)


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 -
Click to expand...

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
 
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.
Click to expand...

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.
Click to expand...

Yeah, but I would go with either Peo's or Teethless mama's as they are
tighter.


Rick
 
Rick, the sql data which gets extracted appears in certain cells, and
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?
Click to expand...

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
 
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?


Yeah, but I would go with either Peo's or Teethless mama's as they are
tighter.

Rick
Click to expand...

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
 
Rick, the sql data which gets extracted appears in certain cells, and
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).
Click to expand...

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
 
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 -
Click to expand...

DANG!!!

Rick, it works like a charm. Now, where do want those pizza's sent?

Thanks so much.

Pierre
 
DANG!!!

So said:
Rick, it works like a charm. Now, where do want those
pizza's sent?
Click to expand...

Glad you liked it. How did you know pizza's are one of my favorite foods?
Thanks so much.
Click to expand...

You are quite welcome. I just want to remind you, though, of the caveat I
mentioned earlier...

....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
 
You are quite welcome. I just want to remind you, though, of the caveat I
mentioned earlier...
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count number of 2008 invoices for customers 2
How to custom format a date 2
counting cells containing certain ranges of dates 1
Date function in ACCRINTM requires date format not available 4
Date format 6
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) 2
Counting once more 2
Combine text with funny date format 5

Back
Top