Converting YYYYMMDD Special Format to Text

What formula or function converts a cell with a special 
date format of YYYYMMDD, lets say 20030801, to the text 
string "20030801"?  

In my case the special format option YYYYMMDD makes the 
number appear as 20030801 but will only display the actual 
date value of 37834 when attempting to convert the date to 
a text string.  
0
mbredal (1)
10/3/2003 3:44:53 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
369 Views

Similar Articles

[PageSpeed] 50

One way:

    =TEXT(A1, "yyyymmdd")

where A1 contains your value.

In article <108901c38960$b4c9b6d0$a301280a@phx.gbl>,
 "Mike" <mbredal@tetontel.com> wrote:

> What formula or function converts a cell with a special 
> date format of YYYYMMDD, lets say 20030801, to the text 
> string "20030801"?  
> 
> In my case the special format option YYYYMMDD makes the 
> number appear as 20030801 but will only display the actual 
> date value of 37834 when attempting to convert the date to 
> a text string.
0
jemcgimpsey (6723)
10/3/2003 3:49:59 AM
Reply:

Similar Artilces:

how to format :send picture with message: as a default
each time i sent a graphic i must go to the format and click "send picture with message." how do I make this a default so it is there permanently.I am running on a Windows XP and am using Outlook Express for my mail. LYNNY <LYNNY@discussions.microsoft.com> wrote: > each time i sent a graphic i must go to the format and click "send > picture with message." how do I make this a default so it is there > permanently.I am running on a Windows XP and am using Outlook Express > for my mail. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Converting Claris Impact files (old Apple files) to M'soft Visio"
Hello I understand the the best way to access old Claris Impact files are to convert using m'soft visio. I'm completely new to this application & will be grateful if anyone can take me throughm the process of doing this. I've had a look at the Versiontracker website on salvaging claris impact files but I couldn't work out how it could help me! I really need some step by step help. I've got Visio 2007 installed on my XP Pro computer Many thanks Perhaps this link might help: http://www.thefreelibrary.com/Orbital+Successfully+Completes+File+Converter+to+Allow+Cla...

Highlighting the active row with special colour
Hi there Is there a macro that highlights the whole active row (not only the active cell) that the cursor is in, in an Excel worksheet? Thanks Raj Highlight it in a particular colour, I meant... that would move along when I would move the cursor onto another row.... "Raj Mazumdar" wrote: > Hi there > > Is there a macro that highlights the whole active row (not only the active > cell) that the cursor is in, in an Excel worksheet? > > Thanks > > Raj Try this '---------------------------------------------------------------- Private Sub Workshe...

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Converting Microsoft Office 2003 Trial into a full version
I have bought a Sony Vaio PCG-GRT360ZG notebook with installed Microsoft Office 2003. I registered Microsoft Office 2003 trial version. Now I am trying to convert it into a full version. Unfortunately I am getting message that my product key (from Certificate of Authenticity) is invalid. My trial version expires in Jan 2005. What should I do ? My product key from Certificate of Authenticity label is : J8FHJ-Q6BJM-G6PVD-FWM2B-PW6J8. Thank you very much Peter. ...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

Converting Adobe PageMaker 6.5 files
Does anyone know if you can convert Adobe PageMaker version 6.5 files so that I can use MS Publisher 2007 to edit these files? I suspect the only way is the copy/paste solution. If you don't have PageMaker, maybe Adobe has help. There is a forum here that might have a solution: http://forums.adobe.com/thread/460491;jsessionid=39FEE1914BD4E0C9DF28733ADF71F413.node0?tstart=0 -- Mary Sauer http://msauer.mvps.org/ "SmithinSA" <SmithinSA@discussions.microsoft.com> wrote in message news:5CCB0500-7E15-4EDC-BC04-775B41F13D8F@microsoft.com... > Does anyone know if you can ...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

can't see text in any office files anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel on my macbookpro: can't see text in any office files anymore (word, powerpoint, excel). only when using preview function from OSX. <br> what's wrong??? <br> do I have to re-install the all suite? any quick fix? ...

Format Problem
I have a report of a crosstab query. I formatted the left column of the report with Text Align set to Right. The report is chopping off the last character of the term, even if I set the Right Margin to 0.5". Doing so just puts space between the right edge of the border and the end of the chopped word I have played with all of the format settings in the properties with no success. If I set Text Align to Left the word is not chopped, but I would prefer Text Align set to right. Is there a way to fix this format issue with Text Align set to the Right? -- Message posted via http://www.acce...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

formatting cell in vlookup
HELP I have an excel spreadsheet that is e:mailed to me by my home office. I use the vlookup feature to find the value in cell b from the information in a Proble At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet. If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any ...

Footnote text cannot be entered without mouse click
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Normally adding a footnote number automatically adds that number to the bottom of the page. A blinking text entry indicator normally allows me to enter the footnote text without any additional step. <br><br>However, today I wanted to add a footnote number after a word, and although the corresponding number correctly appears at the bottom of the page, and the blinking text entry indicator also appears ready to allow entering footnote text, yet when I try to type, nothing is entered. I must resort to ...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

Mottled Text in GIF Image
I'm trying to put a GIF image into my signature page. The image is a Static GIF Image that consists of a graphic and some text. When I go to write the email it appears correctly, however when I send the email the text portion is mottled. I am sending the email to myself and this still occurrs so it is the same monitor, computer, software etc. Anyone have any ideas on what is causing this. Should I covert this image to a jpg or some other format? Thanks in advance ...

Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text format. Is there anyway to have excel recognize that the result of the function is a formula and not text. Once I click on the cell, hit F2 to edit it and then enter, Excel realizes it's a formula and then calculates the value, Nope. The formula in the cell is going to be the UDF--not the what formula string looks like. You could add some more steps. Select the range (if more than one cell) Convert to values (edit|copy, edit|paste special|values) and finally edit|replace what: = (equal sign) with: = replace all And exce...

Impossibly Tiny Text
On rare occasions Excel 2003 (with all updates) screws up and will display just one sheet with impossibly tiny text - like only one or two pels high on the screen. All other sheets continue to look normal. All the cells appear to still have correct information in them, it's just tiny. I can select the entire sheet and tell it to use 100 point type and it will produce type that looks like maybe 10 point type on the other sheets. Or I can copy an entire good sheet, and "Paste Special" only the formats onto the bad sheet and get back the impossibly tiny text again. Saving ...