Numbers Turn Into Dates When Import Them

Apr 9, 2013

Some of my numbers turn into dates when I try to import them. Is there anyway around this problem?

I have 500k+ rows with data, so looking through each and every cell is not an option.

View 2 Replies


Turn Dates Into Month

Dec 1, 2006

I have many dates 1/21/06,2/1/06,3/16/06,1/23/06 etc...I wish to convert into month only such as
1/21/06 ---> Jan
2/1/06 ---> Feb
3/16/06 ---> Mar etc...

I tried month(1/21/06) but it does not work. Is there a function to do this?

View 9 Replies View Related

VBA - Turn Hours To Numbers

Feb 14, 2013

I have a couple formulas in action to give me the total number of hours worked from start to finish.

A1 - 1:30 PM
B1 - 11:45 PM

C1 - I combined the two to get the total number hours worked but set as time: =IF(B1

View 3 Replies View Related

VLookup - Possible To Turn Numbers Into Letters

Dec 13, 2011

VLookups can be used to change letters into numbers, is it possible to turn numbers into letters?

A-7 =vlookup(A,table1,2,1)
7-A =????

View 3 Replies View Related

Import Large Text File Of Numbers Without Excel Splitting Numbers?

Jul 19, 2014

I have a text file with rows and columns of numbers ranging from 1-4 digits that I'd like to import/copy into Excel with each number being in its own cell. But whenever I copy/try to import, Excel splits all of the 3-4 digit numbers up into single digit numbers. The text file has 10,000+ columns (each number occupies two columns so I have half of that amount in numbers) and 300+ lines.

Is the file simply too large for Excel to handle or is there a way I can do this?

View 14 Replies View Related

Turn Column Letters To Numbers And Vice Versa?

Jun 16, 2014

How do i turn column letters to numbers and vice versa

take y values from column and take x values from row

I have 'resolved' values in column A1:A10
I have 'received' values in row B11:K11

I need to fill out a table using the tables axis values stored in the column and row above.

View 3 Replies View Related

Turn Numbers And Cells A Specific Color Automatically?

Nov 18, 2013

Is there a way for me to enter numbers in my cells and have them a specific color? I want all my odd numbers to be bold red with a yellow highlighted cell, my even Numbers to be green with a gray color cell.

I want it to look like this 7 and 8. I can not seem to find anything to show the high light but basically the red 7 will be in a yellow box and the green in a light gray. I will be entering rows and rows of anywhere from 4 to 8 digits

View 3 Replies View Related

Dates After Text Import

Aug 12, 2008

I have imported data from textfile reports generated from a dBase system with the following
Sub ImportData()
End Sub

The problem I have now is that during the operations on the date collumns (G-K) Excel has converted the date to dd/mm/yyyy format, just as I wanted - but has for instance sometimes interpreted the date 10/06/2005 (10 Jun 2005) correctly and sometimes as 06/10/2005 - with no consistent logic I can discern.

I can't get hold again of the source data (the reports) and would need to somehow correct the dates.

The data is historical booking information of a Tour-operator. Therefore there are some hints that would allow me to check if a date makes sense or not. In each row is a start-and an end date in collumns D & E, which would need to be checked for correctness. The following may giv you an idea what I am babbling about: .....

View 9 Replies View Related

Import Dates And Recognized

Aug 13, 2008

I am exporting data to Excel from a 3rd party application - the bulk of which are date entries. For some reason they do not come across as date fields in the cells, and you need to double-click on each cell to correct it. For instance if I try to filter the dates straight after the export to Excel, the cell entries are not recognised.

Anyway, I managed to get around this by running a macro on each column, e.g. for column C:

Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo _
:= Array(1, 4), TrailingMinusNumbers:=True

This only works however if there is at least one entry in each column. Is there a way of telling Excel to skip that column if it is empty

View 9 Replies View Related

Convert Numbers, Representing Dates To Actual Dates

Oct 15, 2009

I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.

View 2 Replies View Related

Convert Numbers Representing Dates To True Dates

Jun 29, 2008

I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters:

In column L I would like to represent the following

if column "B" is "A" add 38 years to column "J" dates are represented as TEXT
if column "B" is "B" add 35 years to column "J" "
if column "B" is "C" add 30 years to column "J" "
if column "B" is "d" add 30 years to column "J" "

Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only.

I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.

View 7 Replies View Related

Turn #DIV//0!” To “0” Or Even Turn It To A Blank Cell

Nov 6, 2009

I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).

The problem is that if there is no data in a participants column then excel correctly gives you readout of “#DIV//0!”. On my results page this #DIV//0!” makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0!” to “0” or even turn it to a blank cell?

View 5 Replies View Related

Avoiding Exponential Numbers During Import

Dec 16, 2008

I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.

e.g. it converts 542690E24 to 5.4269E+29.

Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.

View 6 Replies View Related

Switching Page Numbers During XML Import Using VBA

Aug 4, 2012

I've been programming my VBA script to import data from an XML file which looks like this:

HTML Code:
<response responseStatus="success"> <result xmlns:xsi=""xsi:type="apiPagination"> <currentPage>1</currentPage> <list xsi:type="apiReport"> <Variable1>Name1</Variable1> <Variable2>41</Variable2> </list> <list xsi:type="apiReport"> <Variable1>Name2</Variable1> <Variable2>20</Variable2> </list>

So far everything works great and it imports everything correctly to Excel 2010. This is the code I'm using:

Sub test()
Dim WS As Worksheet: Set WS = ActiveSheet
Dim req As New XMLHTTP
Dim Link As String
Dim PageNumber As Integer
Dim Url As String
PageNumber = 1


The problem is that I only import page1 (only link [URL])

How do I get all URLs so that it also imports the data located on other pagenumbers? For example [URL]

View 1 Replies View Related

Import Single Numbers With Ms Query

May 19, 2009

I'm Importing Columns from one sheet to another in the same workbook. imports good But:

1- Some columns with first records/rows blank do not import at all.

2- I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.

View 9 Replies View Related

Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red

Dec 10, 2008

can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View 4 Replies View Related

Dates Appearing As Numbers

Dec 7, 2009

i had a situation where i import data from web.The sheet contains date & other columns.Whenever,i copy the data and paste it in my worksheet Using Paste Special >> Values i get some numbers instead of Dates.How would i correct the numbers to date format with out loosing the original date format(DD-MM-YYYY). Also the dates are aligned irregularly when i paste them to column.

View 3 Replies View Related

Turns Numbers Into Dates

Aug 1, 2013

When I write out ranges in cells like:


Excel turn some of these ranges into dates such as 01-Oct, which is extremely annoying. But trying to change to different date formats but it still keep it to dates. what I can do?

View 5 Replies View Related

VBA Read Dates As Numbers?

Sep 23, 2009

I have a userform that has several textboxes and checkboxes. Some of the textboxes read and display dates from certain cells. I have the checkboxes conditionally formatted based on the data in the textboxes. However it doesn't seem to be reading the dates as a value. Here's an example of my issue to make it clear for you:

txtBox 1 = 01Sep2009
txtBox 2 = 01Oct2009

What I need is for the checkbox text colour to change from black to red if txtBox1 < txtBox2. Here's the code I have in place:

View 4 Replies View Related

Adding Numbers Using Dates

May 10, 2007

I have a spread sheet that shows the dates items were shipped. When trying to work out the value of parts shipped in a month using SUMIF I get the value zero, I believe this to be because (obviously), there are multiple dates of ships during the month, I therefore tried ignoring the day of ship in the SUMIF but this did not work, the date format is DD/MM/YYYY.

View 9 Replies View Related

Calculating Dates And Numbers

Apr 3, 2007

I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.

The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].

View 6 Replies View Related

Converting Text To Numbers On Import With Quotes ""

Jan 26, 2008

I have a spreadsheet that imports data in column A with quatation marks foe eg "7000", "7101A" etc

I would like VBA code that will convert these into numbers.

View 9 Replies View Related

Getting Dates To Cover Week Numbers

Jan 10, 2014

I wanted to work backwards from a weeknumber (as defined in Excel). In other words, given a week number (i.e. 5) what is the first date and last date of that weeknumber. These are the formulas that I came up with and they work fine.

Formula: [Code] ......

Ffor 1st of the week and

Formula: [Code] .......

For the last of the week.

Attached File : Weeknumber.xlsxβ€Ž

View 5 Replies View Related

Extract Dates From Column Of Numbers

Sep 14, 2013

I have a column of numbers that have certain dates in it. I want to extract the dates and then copy and paste the dates into another column. Shall I use a macro for this or can it be done manually?

Column A1

View 10 Replies View Related

Average Of Numbers Between If They Fall Between Dates

Jan 24, 2013

I have to columns of data

Date and number

10/1/2011 4,002,000

I want to take the average of the numbers between if they fall between dates of 2006 and 2007.

I can not do it with averageifs because I have that NA# in the second column. I dont know what to do.

I have something that works but only if it does not contain NA#, below is the example of what works:


View 3 Replies View Related

Import Multiple Text Files & Copy Each Import

Jan 29, 2008

I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.

However, I have a problem in that my import macro gives me 'Run-time error '1004:

Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!

The code below shows is for the import macro only:

Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

View 8 Replies View Related

Excel 2010 :: Matching Dates With Row Numbers

Aug 5, 2013

I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.

Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1

View 7 Replies View Related

Importing Measurement Fractions As Numbers Not Dates

Nov 25, 2009

I am creating a spreadsheet for use in a manufacturing environment to create a costing and cut list spreadsheet for production set up. The intent is that anyone can use this by copying and pasting an Excel Bill Of Material (BOM) that is generated out of a CAD program onto sheet 1. On sheet 2 (which they don’t touch) is the costing and cut list spreadsheet that references sheet 1 and automatically generated the numbers to create cut sizes and costs.

The first challenge I had was that the measurements include the inch symbol. For example if the imported text 15 5/8" is in cell A1 I am able to remove the inch symbol by using the following formula on sheet 2: =--LEFT(Sheet1!A1,LEN(Sheet1!A1)-1). The result is: 15.6250 which is exactly what I want.

Here is the problem I am having. When the listed dimension is less than 1” and is 5/8” for example I get ###### because I have the cell formatted as a 4 decimal place number. If I change the formatting to number with zero decimal places it becomes 39941 which is how many days it has been from January 1, 1900 to May 8, 2009.

The question is what formula should I use to change the 5/8” imported text so it says 0.6250 and can be used in math formulas? Please keep in mind that the imported text could be a measurement with a small number like 1/16” to a large one such as 284 3/4" and that the imported text “is what it is” and cannot be manually changed or adjusted (because that presents the risk of people creating errors and costing the company money by ordering the wrong material sizes).

View 10 Replies View Related

Stop CSV File Converting Numbers To Dates

Jan 14, 2010

I'm having a little problem working with some CSV files that are sent to us by an outside party. There is a field for an invoice number. This number should be a string since it sometimes has alpha characters and sometimes has dashes. But the field is not delimited to indicate this, so when opening these files in Excel, Excel will sometimes convert the numbers to dates. Once they are converted to dates, there is no going back.

The way we have gotten around this is to change the csv extension to txt, then open as a txt file and convert that particular column to a text column. This causes a lot of additional work as the file ultimately needs to be converted back to a CSV.

Ideally, the people creating the CSV files should create them so the invoices column is properly delimited. But that doesn't seem to be possible right now.

Is there a way to create a macro that would make the modification while opening the CSV file, before Excel has a chance to modify those fields?

View 3 Replies View Related

Dates Display As Numbers Even With Date Format

Jun 20, 2008

dates column displays numbers even though the format in the format cell options is date format.

the number updates to date format only when i do F2 & enter for each cell. i've about 5000 cells with same problem. how cani do them all in a go instead of doing themone for a time.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved