Over 1 million tech questions and answers.

Solved: Excel 2003: Extracting and then converting text to date

Q: Solved: Excel 2003: Extracting and then converting text to date

I am trying to extract a date from a data cell within an Excel 2003 workbook.

The cell contains the data, "October 10, 2011 11:11:11 PM GMT-04:00" but I want only the date information and for it to convert to MM/DD/YYYY format. Basically, taking that cell and turning it into 10/10/2011.

I have about 500 entries with similar data and I am looking for the easiest way to extract the dates from the text. Mind you, the length of the Months (i.e., January vs. September), Date (i.e., 1 vs 11), and even time vary (1:00 vs 11:11).

Any help would be greatly appreciated!

Found the answer I was looking for:

Assuming the first cell is A1,

=TEXT(LEFT(A1,FIND(",",A1)+5), "mm/dd/yyy")

Copy down, then copy the column and paste values.

You can also use

=DATEVALUE(LEFT(A1,FIND(",", A1)+5))

then format the cell as mm/dd/yyy, then copy down.

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2003: Extracting and then converting text to date

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

RELEVANCY SCORE 86.8

Hello,
I have a column of dates that I have formatted like this:
\S\Ayymmdd
The date needs to look like this:
SA160327
This is exactly what I need, except I have to insert these in a csv file, and when I try to copy and paste the date, the formatting is gone, and I just get numbers like this:
42456
Does anyone know how I could copy my dates so that they're just text but in that format?
I'm currently retyping all of them, and I have hundreds....
Any help is much appreciated. Thanks in advance.
Fern.
 

A:Solved: Converting custom date format into text

Read other 8 answers
RELEVANCY SCORE 85.6

I exported a list of invoices to excel from my accounting program. One of the fields, of course, is a date field mm/dd/yyyy.

I am trying to figure out how I can sort, pivot, something with the goal of totaling by month.

I've tried formatting the column to mm-yyyy and text-to-columns to isolate the month and it's just not working right.

Suggestions?

Thanks
 

A:Solved: Extracting or Focusing on the Month part of a Date for a Pivot Table? Excel

Read other 6 answers
RELEVANCY SCORE 84.4

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

A:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

Read other 3 answers
RELEVANCY SCORE 82.4

Hi,

Here is my problem. I'm making a countdown timer in Excel. The process is working fine, but my problem is when I call my Sub to set the time at which I want the countdown to end, I receive the following error:
-------------------
Run-time error '6':

Overflow
-------------------

Here is my code:

Dim TimerEnd as Date
Dim WorldPop as Integer 'This value is picked from my Excel worksheet

TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

Note: WorldPop was tested and the value from the cell is really loaded because I was able to modify another cell value by assigning WorldPop value to it.
---------------------------------------------------------------------

Basically, I store in TimerEnd the time at which I want the countdown to reach 0 (obtained by doing TimerEnd - Now() )

The 1st part of the expression:
(1500 - (3 * WorldPop / 8)) determines in how many seconds the timer should end

The 2nd part of the expression:
/ (60 * 60 * 24) divides the 1st expression to obtain the seconds in decimal value which is added to Now() to get the end time.

What I don't understand is that when I type this expression in a cell of my worksheet that have been formated as date:

=NOW()+(1500-(3*850/8))/(60*60*24)
Note: 850 is just a random number which I tested the result. It can be any number ranging from 0 to 2000

It gives me a valid time in the cell.

What am I doing wrong so that in the code of my subroutine it gives me Overflow?

Is there ... Read more

A:Solved: Excel: Overflow error when converting decimal to date

I found the problem.

The following expression was too long:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

I simplified it to:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / 86400)

and it resolved my problem.
 

Read other 1 answers
RELEVANCY SCORE 80

I am currently logging in Excel customer complaints. Complaints are given a date stamp (in one cell in the form dd-mm-yyyy) at the time they are raised. I am now trying to perform data analysis on the complaints etc.

Is there a way to extract the month and year information from each complaint into one cell (in the form mmm-yy) so I may log complaints per month say?

Regards

Dave
 

A:Excel: Extracting Date Information

Read other 7 answers
RELEVANCY SCORE 79.2

Hi,

I have a column that contains a date and time in the dd/mm/yy hh:mm format
what I want to do is extract the hh:mm bit into the column next to it so I can then sort by the time. I have tried putting in C2: =B2 for example and formatting it as hh:mm which displays what I want but when I sort it it seems to remember the "hidden" dd/mm/yy bit so i have it in time order but for each day, which is not what i want.

I have also tried in C2: =RIGHT(B2,5) but that converts it into a number, regardless of what format I try.

Any ideas?

Gogs
 

A:Excel - Extracting Hours and Minutes from a Date

Read other 10 answers
RELEVANCY SCORE 77.6

Hello -

I see a post replied by macropod from 2012, Titled

"Extracting text from a Word Doc into Excel"
URL:
[SOLVED] Extracting text from a Word Doc into Excel

I have a very similar issue: I have about 3000 MS word documents (forms or you can call templates) used to enter patients informations, finding, diagnoses/results), currently I am trying to find the different patterns, then I need a macro to run through the (say 5 to 7) different template styles to parse into EXCEL fields.


Thank you
Mike

Read other answers
RELEVANCY SCORE 77.6

This site looks great...thought I saw question a while back when I googled here... After reading thru many programming questions here, I realize I know lots less than I thought! ha. Think its a simple Excel formatting, but its been a while.

I've downloaded UK bank files to Excel, and need to change all dates to US format (12/6/2012 to 6/12/2012), with their corresponding text & numbers columns, and then sort top to bottom (oldest to newest), rather than how they came in (newest to oldest) to complete 2 yr. accounting summary. Excel 2007 on Windows 7 laptop.

Thanks much, guys! Jillian

I've included a sample of the columns from Excel sheet. As you can see, there are 5 columns, (oops maybe it won't show as cols) so all corresponding info has to move with the date, when I sort earliest to latest, after changing format for the 2 yrs I have to do. The format options given did not work, even tho' it IS indate format, not text, but when importing, it was already imported as US, not UK. Need to change. Not sure how to set that up, and have all info follow on corresponding cols. Thanks.

27/06/2012 20-71-64 5346xxxx -3.7 PAYMENT EAST MIDLANDS TRAI REF 177 1969574810 BCC
26/06/2012 20-71-64 5346xxxx -28.16 PAYMENT W M MORRISON PLC REF 174 7731688810 BCC
26/06/2012 20-71-64 5346xxxx -13.99 PAYMENT SAINSBURY'S S/MKT REF 174 7884931810 BCC
26/06/2012 20-71-64 5346xxxx -10 PAYMENT LUL TICKET OFFICE. REF 177 7365217810 BCC
25/06/2012 20-71-64 5346xxxx -422... Read more

A:Solved: Excel date format from UK to US & accompanying text files

Read other 16 answers
RELEVANCY SCORE 77.2

Now I need to calculate a date from a given date using 13 workdays prior if the given date is a Monday and 14 workdays prior if the given date is a Wed.

Is that possible?

Thanks
 

A:Solved: Excel 2003, Calculating a date using ifs

This will give you the dates for those and a blank for any other days:
Assuming your date is in column A
=IF(TEXT(A1,"dddd")="Wednesday", A1-14,IF(TEXT(A1,"dddd")="Monday",A1-13,""))
 

Read other 1 answers
RELEVANCY SCORE 77.2

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates on the attached worksheet.

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.
 

A:Solved: Date Alert Excel 2003

I have attempted conditional formatting and am getting nowhere.Click to expand...

should do it
this will test for 31 days less
in one of the conditional format rules put in 3 rules
=if(S2="Y", true, false) make green

=IF(AND(TODAY()>C2, TODAY()-31<C2), TRUE,FALSE) make amber - within 1 month

=IF((TODAY()-31>C2), TRUE,FALSE) make red

see attached spreadsheet - tests Column C2
 

Read other 3 answers
RELEVANCY SCORE 77.2

I am using Excel 2003 windows XP Pro. I am having difficulty filerting the dates. I want to be able to sort different data using the mm/d/yyyy format. Is there a way to do this? Thanks
 

Read other answers
RELEVANCY SCORE 77.2

I need to use formulas to caluculate a given number of workdays before a given date.

For example:
A project is due on August 31

I need to figure
2 workdays before that date
7 workdays before that date
13 workdays before that date
26 workdays before that date

Can anyone help?

Thanks
 

A:Solved: Excel 2003, Calculating a date

Read other 7 answers
RELEVANCY SCORE 77.2

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates .

PQRS1FROMTOMid-Year DueM-Y Complete201-Dec-1130-Nov-1231-May-12Y301-Nov-1131-Oct-1201-May-12Y401-Jul-1130-Jun-1330-Jun-12Y530-Jul-1230-Jun-1313-Jan-13N601-Jun-1231-May-1330-Nov-12N701-Feb-1231-Jan-1301-Aug-12Y

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.
 

Read other answers
RELEVANCY SCORE 76.4

Basicly all I want to do is get a count of entries in a list, if the date in the cell is less than today. The formula I have been trying to use is =COUNTIF(O73,(O73<TODAY())) but its not working so I am obviously doing something wrong can anybody help ?

I have a workround by using a hidden column and a few other arguments etc. but its cumbersome I like the elegance of a simple formula and it should be easy !
 

A:Solved: Excel 2003 - COUNTIF() Date Comparison

The syntax for countif is =Countif(range,test)

Range should be a range of cells, your formula will at most return 1 if O73 < today.
If you want to count all the ones between O1 and O100 you'd put O1:O100 in the place of range.

Test works on the cell being inspected automatically so you don't need to repeat the cell. What you do need is quote marks (not parentheses) around the criteria. For example to count the numbers in O1 to O100 whose value is less than 4 you'd write =countif(O1:O100,"<4")

Because Today() is itself a function it doesn't go within the quotes. It is joined to the less than sign by an ampersand. So in your particular case you'd want

=COUNTIF(O1:O100,"<"&TODAY())
 

Read other 2 answers
RELEVANCY SCORE 75.2

Hi, I'm a beginner when it comes to formula for selecting date ranges! I have tried =SUMIF combos but and hitting a wall. Any help would be very welcome. Pretty basic the request.

2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

Just tired this =SUM(IF(D,">1995",E:E)(D,"<1990",E:E)) but did not work.

[I do have a slightly more complicated request of using real dates and being able on select all for the last 15 years for example and 15 to 20 years, but I think we should leave that for another day}

Any help very welcome, thanks.
 

A:Solved: Excel 2003 - Selecting data within a date range

Read other 16 answers
RELEVANCY SCORE 75.2

In cell A2 I want to display the date that the worksheet was last modified.

Is there a function that does this?

Or is this a job for VBA? If so , how do I do this (from scratch)?

TIA, TTFN
 

A:Solved: Excel 2003 - add worksheet date last modified to a cell

Read other 6 answers
RELEVANCY SCORE 74.4

Hi again,

This one should be much easier than my last issue ...

I'm writing excel macros and everything is going great except that when i tell my macro to save a particular file i need it to add that day's date (the current date) to the filename...

Any takers?

LTT

Oh yeah, Merry Christmas and Happy Holidays to everyone at Tech Support Guys!!
 

A:Solved: Excel 2003 VBA code to Auto Date file names?

Read other 7 answers
RELEVANCY SCORE 74

I have a large workbook (over 4 meg) with multiple sheets and lots of formulas. Today, I was trying to change a cell formula and instead of placing the result in the cell, the formula was placed in the cell and displayed as text, complete with the '=' sign in pos 1. The Evaluation tool states that the cell contains a constant.

The following is an example. The original formula and the mod with the changed component in red.

Original cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$2:$V1265,0)&":T"&ROW())). The cell result is a number.

modified cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$1:$V1265,0)&":T"&ROW())) . The cell displays the formula, exactly as shown, as text and therefore no result.

Has anyone ever encountered this before and is there a solution?
Thanks, Tom
 

A:Solved: Excel 2003: Formula Cell Becomes Text After Mod.

Read other 7 answers
RELEVANCY SCORE 74

I opened 2 of my excel 2003 spreadsheets and found
(a) the row heights were compressed
(b) none of the text in the cells was visible - although I could still see it in the Formula Bar

Has anyone seen this glitch? Can I fix it?
Thanks -
 

A:Solved: Excel 2003 text invisible in cells

Read other 7 answers
RELEVANCY SCORE 74

Hi all,

I am currently using Excel 2003.
I have a column of dates which display the following format 17-Feb-2006. I would like to copy theses from one cell to another, but not in a date format, but as text. Problem is, if I copy into another cell and change the cell format to text, it diplays the cell valuation of 38765.
I also try the data>table to columns route and it does show the cell as text but in this format: '02/17/06'

I believe in excel 97 I could simply copy>paste special>text only, but this seems to have been removed from excel 2003.

I have also simply tried to insert an apostrophe at the beginning of the data in the cell, but this also goes on to display the following format; '02/17/06'

Does this make sense and if so, can anyone pleeeease help.

Many thanks
 

A:Solved: Excel 2003 - Copying dates as text

Read other 7 answers
RELEVANCY SCORE 74

I have used your website many times and to date have found answers to my questions without having to post one.

I will try to make this clear as mud.

I have created a spreadsheet that has only text. On the first sheet is a list of employees, the following sheets are timecards for each construction crew. The formula ='2950'!C48 gave the correct information: Robert . It worked great until I moved employees around. Then then formula changed to reflect where I moved Robert, rather than remaining with the C48 (which is what I want).

I tried using this: =INDIRECT('2950'!C48) but it does not work, as I get #REF! .

How can I have the formula always reference C48 and not change when I move employees around on Sheet 2950?

Thanks
 

A:Solved: Excel 2003 INDIRECT to text on another worksheet

try inverted commas around the reference
so
=INDIRECT("2950!C48")
 

Read other 3 answers
RELEVANCY SCORE 74

Hi All (please refer to entry #4 with updated excel)

Thanks for helping.

Thanks to Turbodante, I am able to sum a range of colunm data in a macro (refer below), but now I need to be able to choose how much of the column to sum using a date that it stops at.
So rather than doing the whole table, I need it to just sum, as per the example attached, from July09 to the top of the table. Also each month additional data will be added, so its needs to be able to keep summing the data from the date to the top of the table as new data is entered each month.
Also different sheets will need to pick up the date where it needs to end. This will be in the worksheet as a manual entry (refer cell E18)

Sub Macro1()
Dim bRow As Long
bRow = Range("b4").End(xlDown).Row
Cells.Find(What:="Cell Name").Offset(1, 0) = "=sum(b4:b" & bRow & ")"
End Sub

Thanks for your help

Strybes
 

A:Solved: Microsoft Excel 2003 Sum Section using date to determine range help needed

Read other 10 answers
RELEVANCY SCORE 73.2

Dear guys,

I was wondering if there is a way to change Columns to Text. I know that from the last question i posted here a while back, that we could change a string of text in to many columns. So what i would like to do now is the opposite.

Referring to the excel spreadsheet attached, I basically want to change columns A, B and C in to G.

At first i tried using the formula "=(A3 x B3 x C3)" and obviously that didnt work if not i wouldnt be posting here. How would you pick values up from a cell and then use them in another cell something like =A3 which will bring the value of that cell into another cell. How would you do it for multiple cell references?

Thanks in advance!
 

A:Solved: Excel 2003 - Picking up Values or Columns to Text

Read other 6 answers
RELEVANCY SCORE 72.4

I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated
 

A:converting numbers to text in Excel

Fatmah,
I could not find a Worksheet function to do this, but depending how many changes there are and how many different numbers need to be changed, you may be able to do this with a VLOOKUP.

In a test I ran I had numbers with values from 1-12. I created a 2 column range elsewhere on the spreadsheet. The 1st column had the 1.2.3 numbers and the 2nd column had their text equivelent. I inserted a new column to the right of where the original numbers were and entered the following to the right of the first value I wanted to change. The vlookup looked like this =VLOOKUP(A4,$F$4:$G$15,2,FALSE) Note: A4 was the cell that had the value I wanted changed. $F$4:$G$15 is the range of the lookup array. 2 is the column with the text value.

Needless to say, this would not be a good idea if every cell had a different number or there were thousands of possible numbers to be converted, but if you had thousands ofmnumbers in a limited range, this might be helpful.
<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Fatmah:
I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated<HR></BLOCKQUOTE>

 

Read other 1 answers
RELEVANCY SCORE 72.4

Hi all,

I'm having a bit of a problem in Excell. Whenever I save an excel file as text, it places quotation marks arrond any cell which contains a comma.

Has anyone ever encountered this or know how to resolve it?

Thanks
 

A:Converting Excel to text file

Read other 12 answers
RELEVANCY SCORE 72.4

Hi, This is silly but...

I have created a template on excel for printing my company cheques and what I wanted to know was:

Can I create a formula or macro so that when I write a number in text (such as 'one' or 'two' or 'three', etc) in the cell representing the word part of my cheque, can I get that number (as '1' or '2' or '3', etc) to appear in another cell representing the numerical part of my cheque.

This is a lazy man's burden but I thought it would also suffice as a check that the right amount was going on both parts.

Sorry for the bother but ...
 

A:Converting text to numerals in excel

Take a look at numbers alphebetic and numeric
 

Read other 2 answers
RELEVANCY SCORE 71.6

Under the title of the portion of the worksheet that I am working with, I want to use text to describe the worksheet that is to follow. I don't want a box that refers to one cell. I want to write continuously, the whole width between the margins of the page, on multiple lines if necessary, just like I was in a text document. I have tried every way I can think of, but can't get it to do what I want - something like how a title works, but without the centering. I want this space to be independent of the rows and columns that are set up in the worksheet. I know there is alt-enter, and I've tried wrap-around. I am hoping that this can be accomplished without importing to or from Word.
 

A:Solved: Windows xp, MS Office 2003, Excel. Using text to describe contents of worksh

Read other 10 answers
RELEVANCY SCORE 71.6

I need to convert an excel file to a quoted comma-delimited format as a text file. When I use the formula:
=concatenate("""",A1,"""",",","""",B1,"""",",","""",C1,"""",), I get an error message "error 528'. I was told in a previous forum to use this formula in the first cell next to the numbers to be converted.
I have 1000 rows of 3 colums of numbers that have to read as follows:
"1","400100000311","52"
I can get it to a text format without quotes, but need the quotes as above.
I'm running windows 7, and open office.
Thanks for any help you can give me.
 

A:converting excel to quoted text file

Read other 13 answers
RELEVANCY SCORE 71.6

I am trying to combine 2 spreadsheets. One acct number col. is formatted as text, one is formatted as numbers. When I convert the text format to number or vice versa, and I try to sort the entire thing as one column, it will not sort them together. Some part of the old formatting is causing a problem. Any suggestions on how to fix?
 

A:Excel: converting number to text format

Excel could just still be seeing some of your numbers as text. Here's how to fix it.

Put a 1 in any blank cell--that's a number one.

Copy that cell.

Select all of your account numbers. Hit Edit-Paste Special and select either multiply or divide. If Excel is seeing some of these numbers as text, this trick will force it to see them as numbers.
 

Read other 1 answers
RELEVANCY SCORE 70

Hi everyone,

I really need help with something that's driving me crazy. I am working on a spending report where I have a list of bank transactions on the 1st worksheet, and then subsequent worksheets are for each month of the year and other data on them.

I created this whole spending report at home on Excel 2007; however, at work where I need it I work on Excel 2003. I need help converting the below SUMIFS formula to a valid Excel 2003 version.

=SUMIFS('Master Spending Trans Summary'!C:C,'Master Spending Trans Summary'!A:A,'Jan. 2012'!A12,'Master Spending Trans Summary'!B:B, ">=" & "1/1/2012", 'Master Spending Trans Summary'!B:B, "<=" & "1/31/2012")

Whenever I've tried to convert it using SUMPRODUCT or SUM(IF, I keep getting either #NAME? or a #NUM! error.

Anyone who knows how to convert this formula, please please please help me!!

Hopefully someone can help convert this quickly, as I need it soon!

Thanks in advance!!
 

A:Help Converting SUMIFS to Excel 2003?

It's an old postin but I found this link that will help you out

http://chandoo.org/forums/topic/converting-sumifs-to-excel-2003
 

Read other 1 answers
RELEVANCY SCORE 70

Question for everyone:

I am trying to cut and paste date from a webpage to an excel spreadsheet. I have tried a few methods but I always get the same problem of text turning into dates. For example I am trying to copy various Win loss records of sports teams such as 5-3. The problem is that 5-3 is displayed as May-3 or other date formats. I have tried using format cells and selected text but that just turns the date into a random #. If I format the cells before I paste the text it still comes up as a date. Any suggestions would be greatly appreciated.

Glad to find these forums. Hope I can contribute in some way!
 

A:Excel date/text question

Read other 8 answers
RELEVANCY SCORE 69.6

Hi.

I have a userform which reads from a text file stored on a network device, and displays the text in a textbox.

A suggestion has been made by a colleague who wants to be able to perform a search on this textbox to find certain keywords. No other suggestion has been made, and I am looking into whether this is possible?

Is there anyway this can be implemented?

Any help would be appreciated.

Kind Regards

Lightingman2003
 

A:Excel 2003 - VBA Search through text within a text box

Read other 6 answers
RELEVANCY SCORE 68.8

Hello All,

I've hit a stump again. Need help figuring out a way to extract the VN#'s from text in a cell. Trouble for me is that there is now constitency in the length of data, so I'm unable to use the Left, Trim, Mid, or Last functions. Uploading dummy file for your assistance. Thanks in advance!
 

A:Solved: Extracting VN# from text

Try the macro below. It will write the exracted VIN number to the adjacent cells in column B. Change the number 1 highlighted in red to reflect your true starting row number.
Code:
Sub FinVIN()

For i = [COLOR="Red"]1[/COLOR] To Cells(Rows.Count, "A").End(xlUp).Row

Range("B" & i).Value = Left(Trim(Mid(Range("A" & i).Value, InStr(1, Range("A" & i).Value, "VN#") + 3)), _
InStr(1, Trim(Mid(Range("A" & i).Value, InStr(1, Range("A" & i).Value, "VN#") + 3)), " ") - 1)

Next i

End Sub

 

Read other 2 answers
RELEVANCY SCORE 68.4

I need to convert Excel data into ASCII comma delimited text file format. I know that if I save as csv. that the data can be pasted into Wordpad with the comma delimiter, but how do I get the quotes around the data fields?

Any help would be appreciated.

Thanks
 

A:Converting Excel data to ASCII comma delimited text file format

Read other 9 answers
RELEVANCY SCORE 67.6

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 

A:Excel 2003 macro code converting comma delimiters

Read other 9 answers
RELEVANCY SCORE 67.6

I have a date at the top of my spread sheet that automatically updates daily. I also manually input dates into cells, they do not change once I have entered them in. How can I make the inputed dates turn red once they have gone past 90 days of the automatically updating date? This is for Microsoft Excel 2003, thanks.
 

A:Microsoft Excel 2003 Date Help!

Use conditional formatting and create a new blank rule based on a formula. I have the =Today() in cell A1 for current date. In A4 I put an arbitrary day.

=$A$4<=$A$1-90 and set the formatting to red and bold.
 

Read other 1 answers
RELEVANCY SCORE 67.6

I am trying to import an excel file into access. Everytime I import it two of my date columns get imported as the date serial number instead of the date value. All of my other date columns import just fine, and are formatted the same within the excel file. The two columns that import in the wrong format have "Unknown" in place of the date. What can I do to get the dates to import as a date value and the "Unknown" cells to import as "Unknown" into access?
 

A:excel 2003 date values

As you&#8217;ve figured out, it is these &#8216;unknown&#8217; entries which is causing Access to change the rest of the column to their serial numbers. Access is very particular in the data formats of each column, so the fact that dates are mixed with text does not sit right with it.

I would suggest that you leave those unknowns as blank.

Also, I believe OBP will be along shortly to correct what I&#8217;ve said.
 

Read other 1 answers
RELEVANCY SCORE 67.6

-I am having trouble with date formats.
-I can format cell, and type of format date shown, but the date will convert say from 040808 to 08/18/2010, oh that auto date format Excel is doing only changes the date by about 4 months,10 days & 2 years off what entered, after Excel converts to date format.
-it happens no matter what date style I chose in the cell format; ie. Mar 31, 2008; or 02 mar 08, or any of the options date format offers.
-any idea of why? it's not the tools>options>calc>1904 issue..already tried that solution...could the base date (1904 data corruption maybe) that the excel program uses have been altered?
-or any other suggestions.
 

A:date conversion in Excel 2003

Read other 9 answers
RELEVANCY SCORE 67.6

I'm trying to format the date entry in a cell (in MS Excel 2003) (e.g. from 20-Jan-2012 to 1/20/2012)using the "Format" feature, but it doesn't work. I wonder what's the problem.
 

A:Date Formatting in MS Excel 2003

Hi, welcome to the forum

Have you tried the Custom format?

Choose under Format Cell the Date format then click Custom and put is as you require, should work

I've attached a sample with three formats to explain it
 

Read other 3 answers
RELEVANCY SCORE 67.6

I have an existing Excel spreadsheet that contains the date format mm/dd/yyyy. I want to convert it to mm/dd/yy. Excel will not convert the current cells to the new format without re-entering the information. Can anyone help? Thanks in advance.
 

A:Excel 2003 Date format

Read other 12 answers
RELEVANCY SCORE 66.8

Hi All,
Is there a way to use the .Find method (or other) to find each date within a range (eg "1/1/2012" to "2/2/2012")?
 

A:Searching for date range using Excel 2003 vba

Can you explain in real life terms what exactly you are trying to accomplish. A sample workbook would also be helpful.

Rollin
 

Read other 3 answers
RELEVANCY SCORE 66.8

Hi,

Just noticed if I insert Jun-15 in a cell i get 01/06/2015 in the formula bar (also noticed that entering june15 etc, defaults to jun-15.

But entering 15-Jun gives 15/06/2006 in the formula bar (all ok)

My Regional settings are set to UK

This normal & built into Excel?
 

A:Excel 2003 - inserting date question

Read other 6 answers
RELEVANCY SCORE 66.8

I am trying to find a formula for counting up to a calendar date from a calendar date. That is, column A, row 1 is the umber of days to count up. Column B row 1 is the date I want to count from, Column C row 1 is the result. It would look like this when finished

33 1/3/2008 2/5/2008

Is such a formula possible?

Michael
 

A:Excel 2003 formula for adding to date

Well this does it, but I am not sure if you should use a date diff function
=B1+A1
 

Read other 1 answers
RELEVANCY SCORE 66

Hi Guys
I am new to this forum and not very technical minded so please bear with me.

I have a monitoring spreadsheet through which i monitor c40 word docs. The biggest issue i face is that these words docs have a box with an expiry date on it. I normally have to open each word doc every week and update the date to current date. Then i go back in spreadsheet and manually update a column which has conditional formatting and goes red to alert me. For my purpose i will have the same date in all word files

WHat i want to achieve is to have a button in my excel file which when pressed updates the date in all word docs to current date. Word files will be closed during the update

I hope i am making sense

Thanking yo uin advance

Regards
 

Read other answers
RELEVANCY SCORE 66

Hello!

I'm hoping someone here can help me with this Excel issue.

I have a few Excel 2007 workbooks with thousands of rows of data over 80 columns. About fifteen of these columns are date formatted. In 2007 the format is perfect. I convert it to 2003, save and close the 2003 version. Open the 2003 version back up and ALL the date columns have switched to general. (shows only 42293, etc).

Changing the fields to date doesn't seem to do anything as the moment I close and reopen the excel file it is converted back to General. Its like Excel 2003 is storing these values as Number instead of Date.

My first short-term solution was to copy the entire spreadsheet and Paste-Special into a new workbook but I am supposed to use these files month after month. My second solution was to manually enter an apostrophe before each date but that caused problems as the field was then read as a text field instead of a date field. Plus it was majorly time consuming.

I don't seem to find a reliable solution to this issue on the Microsoft website and I've only seen one other example that gave the above solutions as their replies. I'm stuck...

Thank you for your help!

Renea_dell
 

A:Excel 2003 Formats ALL date columns as General

Hi there,

Strange indeed. Is this in one of the files, or all of them? Do you have any macros in the file? I can't recreate your error. Can you recreate this in another file? And if so, can you post it?
 

Read other 1 answers