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?

A1: 10410

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

Rick

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

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

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

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

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

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

=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

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