Stop Excel From Changing Numbers To Dates In CSV File?
Jun 6, 2005
When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
View 3 Replies
ADVERTISEMENT
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
Jun 20, 2007
The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?
View 9 Replies
View Related
Mar 1, 2012
I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.
But the two columns are not the same? I want to copy the same random numbers into the other sheet.
Code:
'Generate random numbers
Lastrow1 = Range("c65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=RAND()"
Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"
'Finding a specific row number and copying random numbers
[Code] ........
View 5 Replies
View Related
Sep 25, 2013
I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
View 2 Replies
View Related
Jul 9, 2012
I have looked at some threads related to this issue but don't quite fit what I am on about. I access some data online that appears in a grid format which I copy and paste to Excel. I don't have access to the source data/database. I select and set some query variables and run the query and the result appears in the grid. I copy and paste to Excel that is it. When paste to Excel 2010 I have the choice between "Keep source formatting" and "Match destination formatting". OK, I always choose the 2nd option i.e. "Match destination formatting". That is it. It appears to be a simpler format. Where does the problem come in? Can you help me stop Excel from involuntarily converting numerics to dates when not asked?
The data itself can (at least the part that I'm interested in can) come in 2 different ways. 1) WPS or 2) W%P%S. In case you're interested these represent racing statistics where 1) WPS = Number of Wins, Places, Starts given as whole integer format and 2) W%P%S = Percentage of Wins, Percentage of Places and S is still the integer number of starts. Now, when I run the query and copy these statistics over to Excel, particularly in the form of the 1st type of data i.e. WPS they are in the form e.g. "2-6-12" which means 2 wins, 6 places from 12 starts. The stats literally come across in the form "W-P-S" where I fear the delimiter "-" is causing the date conversion consequences that I have outlined at the start. ALSO, as you could have imagined, I have tried importing the data in terms of the 2nd type of data namely "W%-P%-S", which, on the surface of things begins to solve the problem in terms of the initial paste of the data into Excel. That is true, and yes I have found that this has delimited the data correctly mainly. Although there are still occasional problems. When I import the data this way, I still want the data in the % form as well as the integer form. So I do a great deal of data massaging with what I have and yet in re-converting some of the data back to integer form I can still encounter the "convert to date" problem.
View 4 Replies
View Related
Jan 11, 2009
http://www.excelforum.com/excel-gene...cognition.html
and im copying and pasting data from a website ( football scores )
and when i get what should be 1-1 it returns 01-jan and this i dont want
i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages
wondered if anyone could work out some syntax to use as a macro button?
claymation had a go but it doesnt work.
View 14 Replies
View Related
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies
View Related
Sep 9, 2007
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies
View Related
Oct 3, 2008
I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
View 9 Replies
View Related
Oct 19, 2013
I am working with a 2010 Excel spread sheet and need some direction.
One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.
I need all of the dates to be in the first format (dd/mm/yyyy).
Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?
View 14 Replies
View Related
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
Apr 29, 2009
I have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?
eg.
12
15
15
25
20
should become
1.2
1.5
1.5
2.5
2.0
Is there a way in Excel to do that?
View 3 Replies
View Related
Feb 5, 2014
I've got a main workbook (excel 2007) with a main spreadsheet where i group all the information (text and values) about brands and models linked to 10 workbooks (brands) with several spreadsheets (one spreadsheet=one model).
On the main spreadsheet I've got 16 lines for each model which they have the follow formulas, wich are linked to other workbook / spreadsheet (Mar/Gato)
1)
IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE));"";VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE))
and will return text values
2)
IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;82;FALSE));"";VLOOKUP(V844;[Mar.xlsx]G!$A$3:$CE$78;82;FALSE))
And will return number values
3)
IF(M844<>"";HLOOKUP($V$3;[Mar.xlsx]Gato!$A$2:$CE$78;2;FALSE);"")
And will return number values. The return value (;2 goes from 2 to 16.
This process repeats to all the 10 workbooks e and 250 spreadsheets
4) Table Array of the model spreadsheet
With regard to formula 1) and 2)
The problem is that the table array (on the model spreadsheet) could have diferent dimensions (becouse the launch year of the model) like this:
A$3:$CE$78 (2009 - Launch year)
A$3:$BR$78 (2010 - Launch year)
A$3:$BE$78 (2011 - Launch year)
A$3:$AR$78 (2012 - Launch year)
A$3:$AE$78 (2013 - Launch year)
And the return column (value) for formula 1) is always ;3;
And the return column (value) for formula 2) are like this:
;82; (2009 - Launch year)
;69; (2010 - Launch year)
;56; (2011 - Launch year)
;43; (2012 - Launch year)
;30; (2013 - Launch year)
;17; (2014 - Launch year)
The difference between above values is 13, it corresponds 12 months and the total column (12+1)
For the formula 3) the table array its the same, the only difference is instead of starting A$3, starts in A$2
5) The update of the formulas 1) 2) 3) for 2014
I'm doing this manually... The formulas are updated like this,for 2013, I've got table array A$3:$AE$78 and the return value (;17, then for 2014 i will have a table array A$3:$AR$78 and the return value will be (;30, and so on
5) Change the network drive
For other reasons I've to change my files to another network drive, something like this:
Q:KPI2014Main File
6) Main Goal
I was thinking using Excel's regular Find and Replace feature to change:
- The network drive designation
- the table array area
- The return value
But I don't know if this process ( Find and Replace) is safe and clean. I'm doing this manually and I've got on the main worbook/sheet 4000 lines with 3 formulas its 12000 operations!I'm on the 350 line....!
The main goal is to update the all formulas (links) to "brand" workbooks and inside "model" spreadsheets automatically with a macro.
View 2 Replies
View Related
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
Aug 11, 2014
I have a column in my data set that consists of dates and times in this format: 2014/08/02 01:46:49 PM. I am trying to convert these dates and times to numbers so that I can actually use these values for calculations and regression analysis. When I click on one of the cells, I get a number that is revealed along with decimals. For example, the cell with 2014/08/02 01:46:49 PM had a stored value of 41853.574. I tried to highlight the entire column of dates + times and click on the format cells button. I selected the number category under number but that did not work. I also tried to use the =DateValue(Cell #) function but that did not allow for distinguishing between the same date but different times.
What are my options for converting these dates and times to numbers that I can work with? Is there a way to get the entire column of dates + times to show each cell's numeric stored value?
View 4 Replies
View Related
Mar 19, 2012
1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
View 1 Replies
View Related
Jun 3, 2008
Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).
View 9 Replies
View Related
Jan 19, 2007
Is there a general way to disable a user from changing row size or adding new rows.
View 7 Replies
View Related
Oct 30, 2013
This is the current layout I have:
Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
View 4 Replies
View Related
Jul 6, 2006
If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.
i.e.
Cell A1 has formula = sumproduct(e1:e10>10)
Currently cells E1:E10 are empty
Run the macro
E1:E10 now has data in.
But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)
View 9 Replies
View Related
Jan 21, 2008
I have a userform which is used to update a spreadsheet.
There are 7 textboxes which add dates to the spreadsheet. These dates are then used in formulas.
Every time a record is saved the date format changes.
Ths means that if the date is entered dd/mm/yyyy it writes mm/dd/yyyy and this is fine. When this record is next retrieved it will show mm/dd/yyyy and any new date will be dd/mm/yyyy.
Wen saved to the spreadsheet both formats change meaning the dates in the spreadsheet appear in diffrent formats messing up all formulas.
How can I stop the date format changing each time the record is saved?
View 3 Replies
View Related
Jun 4, 2009
I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?
View 2 Replies
View Related
Aug 26, 2009
When I remove "Release date:" from "Release date:24 December 2008," excel will automatically change the text to "24-Dec-08." Yes, I did try to format the column to "text" before using find and replace.
This wouldn't be a problem, except that half the dates are in Dutch, so only half the dates are changed, which means that I can't make all date formats in a column uniform.
View 2 Replies
View Related
Aug 31, 2006
I am creating time sheet application , obviously i need to store the time when the user logs & logs out .. The issue is, the user can "Cheat" by changing the system time ...
Any alternatives?? Is it possible to store the time from a particular server etc or some other source.
View 9 Replies
View Related
Nov 1, 2006
I am trying to cut/paste a row of formulas in my spreadsheet. The problem is that I want some of the referenced cells in the formula to stay CONSTANT and not increment on each new row. In this example, I want the references to row 17 (F17,E17) to increment (E18,E19,...), but I want the "G7" reference to remain hard coded (as G7) on each row I paste (because that is where my master value is). (The formula is working fine) =IF((F17>0),IF((F17<=G7),E17,0),0)
View 3 Replies
View Related
Feb 13, 2014
Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?
View 3 Replies
View Related
Feb 8, 2010
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies
View Related
Apr 27, 2013
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
View 2 Replies
View Related
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
