View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Elkar Elkar is offline
external usenet poster
 
Posts: 964
Default 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