Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Find date duplicates Col 2 or Col 3 then combine Col 1 text | Excel Worksheet Functions | |||
Combine text and today's date in a cell | Excel Worksheet Functions | |||
Date looks funny! | Excel Discussion (Misc queries) | |||
Combine date with text | Excel Worksheet Functions |