Combine text with funny date format
 

We have some sql source data in an unusual format for a date that
reads (for example): 20070630 (yyyymmdd).

Would like to link to that cell and have the result display a more
readable format; combining some text, and transforning the format of
the date. It would read something like:
"Month Ending 6/30/2007".

Ideas? TIA for them.

Pierre

Combine text with funny date format
 

Try this:
="Month Ending "&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"m m/dd/yyyy")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Pierre" wrote in message
oups.com...
We have some sql source data in an unusual format for a date that
reads (for example): 20070630 (yyyymmdd).

Would like to link to that cell and have the result display a more
readable format; combining some text, and transforning the format of
the date. It would read something like:
"Month Ending 6/30/2007".

Ideas? TIA for them.

Pierre

Combine text with funny date format
 

One way, assuming 20070630 is in A1


="Month Ending "&TEXT(TEXT(A1,"00\/00\/00"),"mm/dd/yy")



--
Regards,

Peo Sjoblom


"Pierre" wrote in message
oups.com...
We have some sql source data in an unusual format for a date that
reads (for example): 20070630 (yyyymmdd).

Would like to link to that cell and have the result display a more
readable format; combining some text, and transforning the format of
the date. It would read something like:
"Month Ending 6/30/2007".

Ideas? TIA for them.

Pierre

Combine text with funny date format
 

On Aug 23, 2:43 pm, "Bernard Liengme"
wrote:
Try this:
="Month Ending "&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"m m/dd/yyyy")
best wishes
--
Bernard V Liengme
Microsoft Excel MVPwww.stfx.ca/people/bliengme
remove caps from email


Thank you Sir. You are a genius.

Pierre

Combine text with funny date format
 

Here are a couple options:

="Month Ending "&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2) ),"m/d/yyyy")

This will give you the day as specified in cell A1, whether it is the end of
the month or not. So, 20070615 would be 6/15/2007.

="Month Ending "&TEXT(DATE(LEFT(A2,4),MID(A2,5,2)+1,0),"m/d/yyyy")

This will give you the last day of the month, regardless of what is stored
in cell A1. So, 20070615 would be 6/30/2007.

HTH,
Elkar


"Pierre" wrote:

We have some sql source data in an unusual format for a date that
reads (for example): 20070630 (yyyymmdd).

Would like to link to that cell and have the result display a more
readable format; combining some text, and transforning the format of
the date. It would read something like:
"Month Ending 6/30/2007".

Ideas? TIA for them.

Pierre

Combine text with funny date format
 

On Thu, 23 Aug 2007 12:29:00 -0700, Pierre wrote:

We have some sql source data in an unusual format for a date that
reads (for example): 20070630 (yyyymmdd).

Would like to link to that cell and have the result display a more
readable format; combining some text, and transforning the format of
the date. It would read something like:
"Month Ending 6/30/2007".

Ideas? TIA for them.

Pierre

This should work:


="Month Ending " & TEXT(--TEXT(20070630,"0000\/00\/00"),"m/d/yyyy")

You can replace the 20070630 with a cell reference.


--ron