Combining text and date in one column

I have a very old spreadsheet originally created in Macintosh Works that has 
entries of either dates (mm/dd/yyyy), years (1986), or text in the cells.  If 
I format the column to text, then my dates turn into numbers.  If I format it 
to dates, then my numbers turn into incorrect dates.  I can format and fix 
the original information, but how can I format the column for future entries?
0
Stacey (160)
3/27/2009 5:50:03 PM
excel 39879 articles. 2 followers. Follow

7 Replies
499 Views

Similar Articles

[PageSpeed] 20

Thank you for the info.  I understand how Excel can show the data in a date 
field if it's not a date and how it shows data in a text field if it is a 
date.  The problem is that the original information is from a database that I 
had to copy and paste into an Excel file for Windows.  I had 3 types of 
information in the column.  Actual text - which comes through the same 
whether I format as date or text; an actual date, which comes through as a 
number if formatted as text; and a year only, that comes through as a date if 
formatted as date.  In summary, my information is mixed.  If I start a brand 
new spreadsheet, format as text, then start typing, I get what I want.  But 
using the existing information doesn't work.  Does that make sense?

"AltaEgo" wrote:

> You do not mention whether your current problem is with data copied to Excel 
> or with the Works spreadsheet. I know nothing about Macintosh Works but 
> suspect the date system will be similar to Excel in which the default system 
> records dates as a number of days since the end of the nineteenth century. 
> If so, this may help you understand how dates work:
> 
> http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
> 
> The main point to understand is that formatting only changes the way you see 
> your data. Formatting does not change underlying values.
> 
> There are links at the bottom of the page that will further help your 
> understanding.
> 
> -- 
> Steve
> 
> "Stacey" <Stacey@discussions.microsoft.com> wrote in message 
> news:58CD60D1-B4CD-4677-A642-F52E0A00A5FD@microsoft.com...
> > I have a very old spreadsheet originally created in Macintosh Works that 
> > has
> > entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. 
> > If
> > I format the column to text, then my dates turn into numbers.  If I format 
> > it
> > to dates, then my numbers turn into incorrect dates.  I can format and fix
> > the original information, but how can I format the column for future 
> > entries? 
> 
> 
0
Stacey (160)
3/27/2009 11:05:03 PM
Well, that's what I was afraid of.  I've tried copying and pasting and 
pasting special, but to no avail.  I guess I should just be happy that we 
were able to get the file converted at all!!  Thanks for your comment.  Have 
a great weekend.

"dranon" wrote:

> On Fri, 27 Mar 2009 10:50:03 -0700, Stacey
> <Stacey@discussions.microsoft.com> wrote:
> 
> >I have a very old spreadsheet originally created in Macintosh Works that has 
> >entries of either dates (mm/dd/yyyy), years (1986), or text in the cells.  If 
> >I format the column to text, then my dates turn into numbers.  If I format it 
> >to dates, then my numbers turn into incorrect dates.  I can format and fix 
> >the original information, but how can I format the column for future entries?
> 
> You can't.  Or, better I should say, you can only provide a format
> that is one or the other, not both.  You have to pick one and then
> manually change it to the other for any entries that need to be
> changed.
> 
0
Stacey (160)
3/27/2009 11:45:02 PM
On Fri, 27 Mar 2009 10:50:03 -0700, Stacey
<Stacey@discussions.microsoft.com> wrote:

>I have a very old spreadsheet originally created in Macintosh Works that has 
>entries of either dates (mm/dd/yyyy), years (1986), or text in the cells.  If 
>I format the column to text, then my dates turn into numbers.  If I format it 
>to dates, then my numbers turn into incorrect dates.  I can format and fix 
>the original information, but how can I format the column for future entries?

You can't.  Or, better I should say, you can only provide a format
that is one or the other, not both.  You have to pick one and then
manually change it to the other for any entries that need to be
changed.
0
dranon (82)
3/28/2009 12:12:44 AM
Why not insert blank columns and then use a macro with if functions using 
"isnontext()", "istext()", and "isnum()" to copy the data to one of the new 
columns.

Then format the columns as you want.

Eric


"Stacey" <Stacey@discussions.microsoft.com> wrote in message 
news:013CE80D-9735-4BBF-AF75-23DC7A466D8A@microsoft.com...
> Well, that's what I was afraid of.  I've tried copying and pasting and
> pasting special, but to no avail.  I guess I should just be happy that we
> were able to get the file converted at all!!  Thanks for your comment. 
> Have
> a great weekend.
>
> "dranon" wrote:
>
>> On Fri, 27 Mar 2009 10:50:03 -0700, Stacey
>> <Stacey@discussions.microsoft.com> wrote:
>>
>> >I have a very old spreadsheet originally created in Macintosh Works that 
>> >has
>> >entries of either dates (mm/dd/yyyy), years (1986), or text in the 
>> >cells.  If
>> >I format the column to text, then my dates turn into numbers.  If I 
>> >format it
>> >to dates, then my numbers turn into incorrect dates.  I can format and 
>> >fix
>> >the original information, but how can I format the column for future 
>> >entries?
>>
>> You can't.  Or, better I should say, you can only provide a format
>> that is one or the other, not both.  You have to pick one and then
>> manually change it to the other for any entries that need to be
>> changed.
>> 


0
ericNOSPAM (46)
3/28/2009 1:17:45 AM
Select the range with years and dates

From the Excel Main Menu:
<format><cells><number tab>
....Category: Custom
....Type: [<20000]0000;[>=20000]mm/dd/yyyy;General
....Click: OK

With that custom number format
• Numbers less than 20000 display as years
• Number >=20000 display as dates

Examples:
2009 displays as 2009
03/27/09 displays  as 03/27/2009
39899 displays as 03/27/2009

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Stacey" <Stacey@discussions.microsoft.com> wrote in message 
news:58CD60D1-B4CD-4677-A642-F52E0A00A5FD@microsoft.com...
> I have a very old spreadsheet originally created in Macintosh Works that 
> has
> entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. 
> If
> I format the column to text, then my dates turn into numbers.  If I format 
> it
> to dates, then my numbers turn into incorrect dates.  I can format and fix
> the original information, but how can I format the column for future 
> entries? 

0
3/28/2009 2:33:59 AM
Assuming none of your dates are before May 18, 1927, the this Custom Format 
should do what you want...

[<10000]General;mm/dd/yyyy

If you have dates prior to that, then assuming none of your dates will take 
place in the year 2100 or later, and assuming none of your dates are before 
September 30, 1905, then this Custom Format would do what you want...

[<2100]General;mm/dd/yyyy

-- 
Rick (MVP - Excel)


"Stacey" <Stacey@discussions.microsoft.com> wrote in message 
news:58CD60D1-B4CD-4677-A642-F52E0A00A5FD@microsoft.com...
>I have a very old spreadsheet originally created in Macintosh Works that 
>has
> entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. 
> If
> I format the column to text, then my dates turn into numbers.  If I format 
> it
> to dates, then my numbers turn into incorrect dates.  I can format and fix
> the original information, but how can I format the column for future 
> entries? 

0
3/28/2009 2:53:26 AM
Wow, thanks everyone for the great ideas.  Can't wait to get back to the 
office and try them out!!  Don't you just love spreadsheets?

"Rick Rothstein" wrote:

> Assuming none of your dates are before May 18, 1927, the this Custom Format 
> should do what you want...
> 
> [<10000]General;mm/dd/yyyy
> 
> If you have dates prior to that, then assuming none of your dates will take 
> place in the year 2100 or later, and assuming none of your dates are before 
> September 30, 1905, then this Custom Format would do what you want...
> 
> [<2100]General;mm/dd/yyyy
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Stacey" <Stacey@discussions.microsoft.com> wrote in message 
> news:58CD60D1-B4CD-4677-A642-F52E0A00A5FD@microsoft.com...
> >I have a very old spreadsheet originally created in Macintosh Works that 
> >has
> > entries of either dates (mm/dd/yyyy), years (1986), or text in the cells. 
> > If
> > I format the column to text, then my dates turn into numbers.  If I format 
> > it
> > to dates, then my numbers turn into incorrect dates.  I can format and fix
> > the original information, but how can I format the column for future 
> > entries? 
> 
> 
0
Stacey (160)
4/2/2009 3:38:01 AM
Reply:

Similar Artilces:

Tracking Dates For Future Occurrences
Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. In 2007 Also.. "Knee2no" wrote: > Can this be done? I want to track a yearly review. I would like the date, > once entered - say 6/1/2009, to conditionally format to change yellow 30 days > before, then red 15 days before, and then to stay red until the date is...

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Hiding empty rows and columns
Does anyone know the code for hiding all blank rows and columns in a worksheet. Thanks -- Message posted from http://www.ExcelForum.com Hi try the following (adapted from: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows) Public Sub HideBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFuncti...

Excel moving horizontal columns to vertical
I am trying to find a simple way of moving data in horizontal columns (referencing the key in column 1) to vertical colums (still referenceing the same key). This would mean automatically repeating the values in column one for every entry in the moved columns. I can do this through the pivot table, but this is a long drawn out process. Any ideas I'm not sure if this will work for you, but you can try this: - Highlight & copy on the horizontal value you want to make vertical - Now highlight the vertical area you want these value to got - Right Click when highlighted & choose...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

How to make a Add key disabled after adding one record unless
Hi, I have a form where I am adding records. There are two boxes where one has to put the lineitemnumber and another box where one has to put the description. Unless the above two boxes are filled in the Add command box need to be disabled. Now, the problem is after adding one record the Add button is enabled and I cannot disable it. I would appreciate any help to solve this. The error message is: The methos is not supported The following is the code: Private Sub cmdadd_Click() On Error GoTo Err_cmdadd_Click If (IsNull(Me.LineItemID) = True) Or (IsNull(Me.Description) = True) Then ...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Text Very Small When Replying
I'm able to change the text size when I'm replying to an email but how does one set the default size? No matter what I ser the font size to when replying it always reverts back to the smallest size when I do another reply. Look, I'm 54! I can't read that small text! James Are you using stationery? Also, Alt + T, Options, Compose,....... -- Ron Sommer MS MVP-Mail "JamesJ" <jjy@darwin_roadrunne0r.com> wrote in message news:OGt2E1OhKHA.1460@TK2MSFTNGP06.phx.gbl... > I'm able to change the text size when I'm replying to an emai...

Extract text from field
If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance. On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in...

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

Converting date from an external source
I am having an issued with converting a date from external data source. the data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I want to convert the date to mm/dd/yyyy format so when i run a query for a single day it will return the data for that date, I can currently return the data but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want to simply return the data by setting the parameter in the mm/dd/yyyy format Don't confuse how data is stored with how it is presented. As long as you import the date into a field defined as a Date data type, you...

Is there a way to sort a column without invoking a macro?
I am trying to get a feel for some probabilistic properties. For example, I am trying to look at the statistics of the intervals between randomly occurring pulses. I have no problem getting a column of uniformly distributed pulse times, but they are unsorted, Every time I recalculate with cmd-=, I get a new unsorted set of pulse times because of the use of the RAND function. Is there a way of taking this unsorted column and pasting it into another colun that would be sorted without invoking a macro? To simplify and be more specific consider the following. 1. In A1:A100 I have a set...

How do I print 4 postcards to one page to match my postcard paper
I bought perforated postcard paper that has 4 postcards per sheet. How do I create that in Publisher? I created it exactly how I want it to look and then realized it's only printing 2 per page. Thanks, Lauren Which version of Publisher are you using? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Lauren" <Lauren@discussions.microsoft.com> wrote in message news:DDA6778A-5DF0-4278-A1CA-896DC49FAFBA@microsoft.com... >I bought perforated postcard paper that has 4 postcards per sheet. How do >...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

greyed out auto text
Created custom toolbar in Word with menu, autotext and email button as directed in "Quick sales letters for e-mail" demo video, but when I click email button, new email message appears in Outlook but auto text choices are greyed out???? Any ideas?? ...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...