If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Using formula to convert date result to text
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Using formula to convert date result to text

#1
August 3rd 12, 04:21 PM
 hbcobra Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 5
Using formula to convert date result to text

Hello,

I used a formula to convert text into a specific date value. Now I would like a formula to convert that result into text. I understand that you can do a past special and only paste the values, but I am wondering if there is a formula to do this so I do not have to constantly re-copy and re-paste when I make changes to the original text strings.

Thank you
#2
August 3rd 12, 04:25 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 658

Quote:
 Originally Posted by hbcobra Hello, I used a formula to convert text into a specific date value. Now I would like a formula to convert that result into text. I understand that you can do a past special and only paste the values, but I am wondering if there is a formula to do this so I do not have to constantly re-copy and re-paste when I make changes to the original text strings. Thank you
I think you need to explain a little more.
What was the value before your current formula did its magic? What is the current formula? What is your desired result?
#3
August 3rd 12, 04:47 PM
 hbcobra Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 5

Quote:
 Originally Posted by Spencer101 I think you need to explain a little more. What was the value before your current formula did its magic? What is the current formula? What is your desired result?
Here is a link to a sample .xlsx I am using on Dropbox:

http://dl.dropbox.com/u/1185462/Sample.xlsx

Currently I am copying the data in column B and pasting it as a value into column A. It would be great if I could use a formula to get a static text value in column A since my original column E values are going to change quite often.

In addition, is there a way to trim the "CAL" and the ".PDF" in a single formula? Currently I have it as two separate formulas (columns C and D).

Thank you very much.
#4
August 3rd 12, 09:05 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 658

Quote:
 Originally Posted by hbcobra Here is a link to a sample .xlsx I am using on Dropbox: http://dl.dropbox.com/u/1185462/Sample.xlsx Currently I am copying the data in column B and pasting it as a value into column A. It would be great if I could use a formula to get a static text value in column A since my original column E values are going to change quite often. In addition, is there a way to trim the "CAL" and the ".PDF" in a single formula? Currently I have it as two separate formulas (columns C and D). Thank you very much.
Enter =MID(E2,4,6) in C2 of your example and copy down. You then don't need column D so it can be deleted. That's your second query dealt with.

I'm still not sure I get what you mean for the first one though. Why do you need to convert the dates to a static value?

Last edited by Spencer101 : August 3rd 12 at 09:08 PM.
#5
August 3rd 12, 11:24 PM
 hbcobra Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 5

Quote:
 Originally Posted by Spencer101 I'm still not sure I get what you mean for the first one though. Why do you need to convert the dates to a static value?
I will be exporting the data in column A to another file. When I try to export the contents of column B, I get an error. For whatever reason, I do not seem to get that error with static values.

Last edited by hbcobra : August 3rd 12 at 11:33 PM.
#6
August 3rd 12, 11:24 PM
 hbcobra Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 5

Quote:
 Originally Posted by Spencer101 Enter =MID(E2,4,6) in C2 of your example and copy down. You then don't need column D so it can be deleted. That's your second query dealt with.
Perfect, thank you
#7
August 3rd 12, 11:56 PM posted to microsoft.public.excel.worksheet.functions
 zvkmpw external usenet poster Posts: 116
Using formula to convert date result to text

> I used a formula to convert text into a specific date value. Now I
> would like a formula to convert that result into text.

The TEXT function may provide what you need.

With a date in A1, try
=TEXT(A1,"Mmm dd, yyyy")
or
=TEXT(A1,"mm/dd/yy")
or
=TEXT(A1,"Mmmm d, yyyy")

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Convert Function Result To Plain Text Questor Excel Discussion (Misc queries) 3 January 29th 10 01:44 PM Convert ADDRESS() text result to cell reference FirstVette52 Excel Worksheet Functions 4 April 30th 08 05:33 PM HOW DO I ENTER THIS FORMULA TO CONVERT TEXT TO DATE =DATEVALUE(, Carter Excel Worksheet Functions 4 March 15th 06 02:18 PM convert drop down text into result PeterHad Excel Discussion (Misc queries) 2 January 11th 05 02:43 PM What is the formula to convert a whole column of date to text? Caine Phorn Excel Programming 2 February 9th 04 08:43 AM

All times are GMT +1. The time now is 04:06 AM.