TEXT(date)?

As you know, the function

=DATEVALUE("8/22/2008")

produces a date from its corresponding text representation.

I want to go the OTHER way. That is, I want to take a date and produce its 
text representation (I'll settle for any reasonable format).

How might I go about doing that?

Thanks,

John


0
jfavaro (8)
3/28/2006 4:55:28 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1043 Views

Similar Articles

[PageSpeed] 29

Your answer is in your question!!

try:
=TEXT(TODAY(),"dd-mm-yy") or any other reasonable date-style format

-- 
Gary's Student


"JMF" wrote:

> As you know, the function
> 
> =DATEVALUE("8/22/2008")
> 
> produces a date from its corresponding text representation.
> 
> I want to go the OTHER way. That is, I want to take a date and produce its 
> text representation (I'll settle for any reasonable format).
> 
> How might I go about doing that?
> 
> Thanks,
> 
> John
> 
> 
> 
0
GarysStudent (1572)
3/28/2006 5:26:04 PM
Well what do you know! I guessed right without even knowing it!

Thanks!

John

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:9B5207A1-757D-43A6-A440-B1E146FE90DD@microsoft.com...
> Your answer is in your question!!
>
> try:
> =TEXT(TODAY(),"dd-mm-yy") or any other reasonable date-style format
>
> -- 
> Gary's Student
>
>
> "JMF" wrote:
>
>> As you know, the function
>>
>> =DATEVALUE("8/22/2008")
>>
>> produces a date from its corresponding text representation.
>>
>> I want to go the OTHER way. That is, I want to take a date and produce 
>> its
>> text representation (I'll settle for any reasonable format).
>>
>> How might I go about doing that?
>>
>> Thanks,
>>
>> John
>>
>>
>> 


0
jfavaro (8)
3/28/2006 5:30:59 PM
Reply:

Similar Artilces:

date flashing
hi, i am asking if you can, if so how the date flashes up in excel. in example if u have a load of cars when there mots are due and you have all the dates in, and i want it to flash up a colour(red) when it reaches that date so you know when to mot it. -- gummertwo ------------------------------------------------------------------------ gummertwo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31741 View this thread: http://www.excelforum.com/showthread.php?threadid=514540 Take a look at Conditional Formatting under Format. HTH Regards, Howard "gummer...

Date and time #3
Can I have excel display the current date and time? Time is not tha important but date would be great -- Message posted from http://www.ExcelForum.com =TODAY() will update with every calculation or manually pressing F9 =NOW() date and time -- Regards, Peo Sjoblom "gsaenz >" <<gsaenz.103553@excelforum-nospam.com> wrote in message news:gsaenz.103553@excelforum-nospam.com... > Can I have excel display the current date and time? Time is not that > important but date would be great. > > > --- > Message posted from http://www.ExcelForum.com/ > ...

Spin Box Date and Sumproduct
Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000))). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containi...

On POS (not RMS) Adjusting Time Clock Defaults to Today's Date #2
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

How to validate time format from a text field?
The goal is to normalize data before converting text fields to datetime fields. There are start times and end times stored in text fields of a table. The problem is that any criteria used in a query causes errors when any function causes text to be converted to a datetime type, and the text field does not contain a valid date or time. I'm familiar with some of the validation functions such as isNull(), but I can't find anything like isTime(). How can I test existing data for valid time format? IsDate works on time as well. -- Doug Steele, Microsoft Acce...

Add text to slides in Power Point
Hello: For the first time I am atempting to create a slide show in Power Point. I already have all the slides, placed a label on each one and saved the .pps file. However, when I played the .pps it shows the pictures but not the text. Could you tell me what to do? Do I have to save each text and how? Thanks a lot. Which version of PPT are you using, and how did you add the labels? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover ...

replying to plain text
Outlook 2003 SP 2 Word 2003 SP 2 How do I set Outlook to always reply in HTML or RTF? When replying to, or forwarding, an email that is in plain text format, it always uses plain text. I want to force it to use RTF or HTML. I understand why it works this way. It is assuming that the user is sending plain text, that they *can't* understand anything else. In my case, I know who I am sending to and know otherwise. I know I can change it every time, and I do, but it is getting very old. Thanks, Brad. Outlook doesn't not have such an option. You'll need to either change it m...

repeat text in same exact cells in multi worksheets
I have a workbook and have 20 worksheets in the book. example of what I would like to do. on the worksheet in the same exact numbered cells I want to put the text in once and then put a forumla to make it copy the info to 2-19 pages of the workbook. like 1 of 20 pages, 2 of 20 pages - I only want to put the "of 20 pages" once then have it when it's changed it would automatically change the other work books if I had 21 pages one time then it would change the others. I hope this makes sense. Thank you! One way This needs to be done once only, select the 2nd to the 20th she...

Comparing a date field with today's date
My report contains a date field 'Delivered_On' and a label 'lbl_Sited'; both have the visible format set to no. I am trying to get the date field to become visible if the date is greater than today's date, and the label to become visible if the date field is less than today. When I use the argument "If Me.Delivered_On >= Now() Then.....", I get an error "You entered an expression that has no value" -- Keith W ...

Change a comma separated text file and save it.
Hello All, I need some help on comma separated text files, changing them and saving them. I have got two text files. The first file "ES_EF_AIG_20141440_105708_d.txt" has employee payment record exactly like below: "C0000000519","","","MR STEPHEN AGUTTER","10 COLCOKES ROAD","BANSTEAD","SURREY","","","","","","SM7 2EW","MAL","AGUTTER","NS686159A","0000153363","CDN","E","EMPLOYEE",&quo...

Extract text strings
To all: I have looked throughout the forum and cannot find the solution to my problem. I have a document that has text strings within single quotes (e.g. 'this is sample text') I need to pull all of these out and place them in another document (or preferrably an excel spreadsheet). If someone could point me to some VBA code, that would be much appreciated. If you have Word 2003 or above, you can use the "Find All" feature to do this; see http://sbarnhill.mvps.org/WordFAQs/SpecialFind.htm#CopyingSelectedText (scroll up from that section to get the ...

code for text to columns
Let's say I want a worksheet to do "text to columns" (delimited) ever time it opens? For example: Text to columns, delimited, by space for column A. Thanks again all...Vat -- Message posted from http://www.ExcelForum.com Hi, Vato, Add this code to your main sheet module1.bas.(In VB editor, menu Insert->Module) Sub auto_open() Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, S...

Formatting dates on x axis of chart...!
Win XP HE Excel 2002 Hi, I have a chart with a simple y and x axis. The x axis represents dates. In sheet1 of the spreadsheet being used, the dates are in column A and in each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the dates are in this format: 12/20/2004.) The result is (obviously) the same number and format 12/20/2004 in sheet1. When I chart, for the life of me I cannot get rid of this format in the x axis labels--I am usually able to define the label format in tha chart itself but, nothing I do lets me change this format. Even when unchecking the "link to sou...

Count first occurance of text
I have a row of 24 cells, some are blank and others have text. I am trying to create a formula that returns the first instance of text being used. I have tried using a match, but it gives me an error because I am trying to pull text not a number. thanks Here's one dart throw .. Assume your 24 source cells are A2:A25 Place in say, B2, normal ENTER to confirm: =INDEX(A2:A25,MATCH(TRUE,INDEX(ISTEXT(A2:A25),),0)) Bullseye? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Chad Wods...

Date conversion issue... again
Hello all, I have a file that contains dates. I know that for example "12.10.2004" means "December 10th, 2005", however Excel treats as "Oct. 12th 2004". Nothing works: converting the format of the cells doen't help, the TEXT(A1;"mm.dd.yyyy") doesn't do anything because it works on the serial number instead Any suggestions? thanks a lot in some other empty cell (without any formt) type this =(SUBSTITUTE(A1,".","/"))+0 you may get the date number format it as you like do you get what you want;. Dimmer <Dimmer@disc...

Evaluate Receipt Date in receipt
Is there a way to evaluate a receipt date inside of a CONDITION tag? I want to do <= and >= I've tried and it seems like I cant evaluate TransactionDate. (Yes, I am using >= and <=) Thanks.... ...

Date Calculation
I need to write a formula or VB code for the following: Say Column A contains dates and column B amounts. In column C, I need to calculate 10% of column B if the date in Column a is between 1 April and 30 September. If the date in column A is between 1 October and 31 March then calculate 15%. The problem is it need to work for any year and the date in column a will be in the format of dd/mm/yyyy. Your help will be much appreciated. Many Thanks in advance. Hi Into cell C2 enter the formula =IF(AND(MONTH(A2>3,MONTH(A2<10))),0.1*B2,0.15*B2) and copy it down -- (When sending e-ma...

Delete columns based on a cell reference date value
I have a sheet titled 'data' that has source information and row 1 has columns H to BG with dates. On sheet 'Control' is a cell that has a free format cell for dates to be entered. I have named this cell 'WCDATA' for ease of another piece of code. I am tring to create a macro that will locate the date specified in WCDATA on the data sheet and delete all columns from H until that which matches WCDATA. Fortunately I have been running on a test file and I have tried several options but after considerable hair pulling am seeking assistance as am getting...

CRM 3.0 Account Last Contact Date
I can't seem to find out how to filter accounts by last contact date. We need to be able to filter those accounts that have not been contacted recently, so we can call them. ...

Choice of Date or another field
I have a column which is used for component completion date. The latest date is teh complition date for teh project. I am using MAX(field:field) to select the latest date. However, we are running across the occurance where a date is TBD(to be determined) How can I choose the TBD if it is present in the column over the max date? Hi! Try this: =IF(ISNUMBER(MATCH("TBD",B1:B30,0)),"TBD",MAX(B1:B30)) Format cell as DATE Biff "Mark" <Mark@discussions.microsoft.com> wrote in message news:7EAD2BA1-AACF-4921-B7EA-248C98E528F9@microsoft.com... >I ha...

Number stored as text
I have number stored as text. It has a small green triangle at the upper left of the cell. If I click on the cell it has a yellowsign with a ! and if I click on that a menu and "convert to number" This works fine one cell at a time. I need to convert columns the cell format has no effect on the column of cell I need to change. I did un click the protect on the last tab. Suggestions? Sueanne copy an empty cell. select your range of offending cells Edit|paste special|check Add sueanne wrote: > > I have number stored as text. It has a small green triangle at the ...

The message not display the body text
Some body help!!! In Ms Outlook 2003 I have 2 accounts set up, 1 with a POP3 access and 1 aol account using IMAP. Suddenly, both accounts are able to connect and download messages, but the messages don't have any body text. When the message is opened, all the information appears in the top of the message form, but the message body is blank. and alert the message box is "The Message not yet download try download and open again" Did something with my outlook 2003?? Best Regards, Chen ...

How do I set up a cell to input a date 1 year from another cell?
I'm entering training dates in Excel. The recertification date is one year from the original date. I want the recert. due date to enter itself when I input original date. Original dates are one row, recerts are another. =A1+365 If your dates were starting in A1 try this formula in B1 "wyrmslair" wrote: > I'm entering training dates in Excel. The recertification date is one year > from the original date. I want the recert. due date to enter itself when I > input original date. Original dates are one row, recerts are another. =A1+365 assuming the orig...

Entering text in a cell.
I have an employee who is used to using Quartro Pro, and we just switched over to Excel. In Quartro, she could enter numbers using the -130. format, that is minus, 130, dot; and press the right or left arrow keys to change cells. In Excel, when she does that, it wants to enter an formula, -130.B3 , the B3 is what ever cell she goes to. Can this be turned off, if so how Garling I have Excel 2002, so this may not be exactly right, but you can go to the Tools menu and select Options. Then select the Transition tab and check Transition Formula Entry. That did it for me. K --- Message poste...

HTML email now displays as text
All of a sudden in the last few weeks, subscription emails that had always come to me as beautiful html pages now come as text, with a bunch of links and no images. I'm thinking maybe it was an update that's caused the problem. I'm using Outlook 2003 (11.8206.8202) SP3 on Win XP. Any suggestions? -- Dena Jo Email goes to denajo2 at the dot com variation of the Yahoo domain. Plonk the bastards: http://www.panix.com/~mwsm/trolls.html We know of an issue on Vista which is fixed by a hotfix (http://support.microsoft.com/kb/951982) I don't see where it says vista on...