Combine text with funny date format

[Pierre]

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

 

[Bernard Liengme]

Try this:
="Month Ending "&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm/dd/yyyy")
best wishes

 

[Peo Sjoblom]

One way, assuming 20070630 is in A1


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

 

[Pierre]

Try this:
="Month Ending "&TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm/dd/yyyy")
best wishes

Thank you Sir. You are a genius.

Pierre

 

[Guest]

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

 

[Ron Rosenfeld]

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