Imported Dates Being Seen As Text
Jan 17, 2008
The problem i'm having is that these data downloads come off the server like this:
01/14/08
so i'm getting the green triangle error "text date with 2 digit year"
with the option to covert it to 19xx or 20xx.
I have to manually select this text and select 20xx before it is recognized by excel for what it is. only after this is the conditional format rule processed, and my 5 day old dates turn their appropriate colors.
When i set up a macro record while i do this date conversion, it does not record any command for this conversion.
Is there any code i can enter that will automatically do this conversion process for me without having to do it by hand?
View 5 Replies
ADVERTISEMENT
May 18, 2007
I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.
Also i do not get the option to convert text to number
View 6 Replies
View Related
Mar 27, 2008
I have 85 Html files that I open in excel. The files have a bunch of columns with numbers. Excel handles most of them properly, but if the number looks like a date, it is imported as a date (which it shouldn't be). For example if the number is 13-1, excel handles it fine, but if the number is 12-1, excel thinks it is a date and imports Dec-01. How do I get excel to import it as 12-1?
View 6 Replies
View Related
Sep 12, 2007
to change the format of some dates that are imported as text.
In a previous request ...
[url]
I was shown a way to convert dates correctly.
This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted.
Here is a small sample of the dates I'm working with...
04/09/2008
05/10/2007
08/10/2007
14/09/2007
15/09/2007
17/09/2007
17/09/2007
17/09/2007
19/03/2008
20/09/2007
21/09/2007
21/09/2007
25/09/2008
27/09/2007
28/09/2007
28/09/2007
And here's the recorded macro, deliberately left exactly as is
View 14 Replies
View Related
Apr 15, 2008
I use a program that can export data into Excel. For the most part, it works fine. except it doesn't correctly handle dates. For some reason, when it pastes a date into excel it includes 8 or 9 (depending if the month is 1 or 2 digits) spaces in front of the date which makes calculations with these dates impossible unless you go into each cell and delete the spaces (or do Find / Replace).
Is there a way I can create a non-VBA formula that will reference any one of these cells and some how identify what the date is & then that cell can be the date I can calc on? I.e., A1 is the date with the spaces in front, B2 is the cell that will reference A1 and produce, as the contents of B2, the date in A1. I can then use B2 in calculations.
Note: the month formats possible are: M/DD/YY (Jan. - Sept.) & MM/DD/YY (Oct. - Dec.). There are no other variations.
View 8 Replies
View Related
Aug 9, 2007
I have dates imported from an external application in the format:
71205
for december 5th 2007, for example
View 9 Replies
View Related
Dec 7, 2007
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
View 5 Replies
View Related
Jan 29, 2014
I have a code that import a txt file to an existing sheet, and works perfectly. I also have a ComboBox named "txtstorey" where users select the level they are working on, ie Ground Floor, Forst Floor etc.
I need to add in a VBA code that add the level that is selected in the cells on the same spreadsheet
For example, the current code import the data like this (It places the data at "$C$1"
Living Room20
Kitchen14
Bedroom 112
Bedroom 210
Bedroom 311
Bathrm4
En Suite5
Hallway3
Garage18
This is what I want:
Ground FloorLiving Room20
Ground FloorKitchen14
Ground FloorBedroom 112
Ground FloorBedroom 210
Ground FloorBedroom 311
Ground FloorBathrm4
Ground FloorEn Suite5
Ground FloorHallway3
Ground FloorGarage18
(Where "Ground Floor" is from the txtstorey ComboBox)
Here is my code as is:
[Code] .....
View 4 Replies
View Related
Mar 10, 2008
I have opened a .txt (by right clicking and selecting Open with rather than open from within Excel) file with numbers using the format 1,234,567.00 but in my country we use 1 234 567,00. So I created a macro changing the , into "" and the . into , and it works fine while creating the macro itself (by using the macro recorder) and the numbers shows and behaves like numbers. However, when running the macro on a new file some numbers remain as text whereas others are numbers. Theres is no common string, eg that numbers starting with - (minus) remains text.
It does then not matter if I go to the format function and select number with decimals and thousand dividor - the number still remains as text. I have also tried the suggested multply by 1 but it still remains as text. The only thing that helps is to either double click on the number or use the Konvert text to number. But I would then have to click each number in the whole table which is what I wanted to avoid.
View 7 Replies
View Related
Apr 10, 2009
I immediately began having problems with formulas and pivot tables when I began importing data from an offsite postgreSQL database. For example, "countif" and "sumproduct" formulas invariably return values of 0. I can create pivot tables from the imported data, but nothing with a numerical value will group.
After fighting with this for literally hours, I finally noticed that all numbers in the columns were on the left side of the column meaning that the numerical data is being imported into Excel as text, even though the columns in the spreadsheet into which the data is imported are in number format.
Any way either to import numbers as numbers from the postgreSQL database, or to convert text to numbers after the data is imported.
View 5 Replies
View Related
Jan 8, 2013
I have imported a jpeg image into Excel but can not figure out how to move it to the "back" so I can add text on top of it. When I make it a "background", it makes duplicate backgrounds when I only want one. Is this the only way and if so, how do I set it so that I only have one image in the workbook?
View 1 Replies
View Related
Oct 18, 2011
I have imported data and it shows "Check 1234". How can I use a formula to remove "check" from this cell?
View 3 Replies
View Related
Mar 15, 2009
I have a text file I am importing and using text-to-colums.
Some of the text is "+TOL" "-TOL" and when excel converts to columns it is forcing the text to a formula "=+Tol" "=-Tol" giving me #NAME? in those cells.
I can format the cells as text, which is fine for now since I'm using VBA, but if I need to use any formulas then I'm SOL...
View 9 Replies
View Related
Jun 28, 2006
I am importing some data from an in house system. This data includes a date, which is imported as text. I have tried to reformat it using the date format options, but this does not work. The date is required as part of a lookup, and whilst it is formatted as text, the lookup does not work.
View 3 Replies
View Related
Mar 25, 2013
I am trying to convert a text imported from CSV file to a number so that I can further elaborate it.
I tried value, trim, clean but without sucess.
Cell values is -10 000,00 what I want to display is 10000.
How can i Do that.
I am attaching a demo file for illustration : Webistexpences.xlsxi
View 6 Replies
View Related
Jul 18, 2013
Following is the master data that's imported from weekly timesheet.
DeptProjNoProjNameEmployee
Dept170000Project1Shawn Johnson
Dept170000Project1Brenda Larry
Dept260010Project2Patrick Leh
Dept170010Project3Shawn Johnson
When imported, the 'ProjNo' is stored in Text format. Now when forecast data is appended to master table, the ProjNo stores as Numbers. I would like to know how I can automatically convert it into Text. The number of records varies every time when imported from the Timesheet.
The other thing is I need AutoFill 'APPROVED' in Column 5 from 2nd row to the last row.
View 5 Replies
View Related
Nov 17, 2009
I have attached two files, one the sample txt file imported to excel but the top area is highlighed with some notes and section break. RED deleted don't need amd un highlighted is actual data. The other file is what I would like to see the data look like after parse.
View 5 Replies
View Related
Mar 24, 2007
I need to clean out unnecessary data from a file, (see attached text file), I'm not sure how to go about this in excel. basically every file starts with 9 cells that needs to be deleted, two cells of real data then one with garbage that needs to be deleted, it goes like that for 40 cells, then again 10 cells of garbage that needs to be deleted, then 40 of real data and goes like that up to 3000 lines, I know it sounds confusing but if you take a look at attached file, at the end I need to have
all cells full of data
View 5 Replies
View Related
Aug 19, 2007
I need to bring in 2600 student names from a cvs file and have it formatted to an Excel workbook.
When it imports into the Excel file I need it to populate the proper boxes, such as:
StudentLastName
StudentFirstName
StudentID
GradeLevel
TeacherName
TeacherID
RoomNumber
I have the CVS attached. Hope I can get this done. As you can see it shows the teacher's name with their first initial, their teacher number that they are identified by and their room number. I need the student ID as well but not the phone number and the days of the week. This would save a lot of work.
View 9 Replies
View Related
Nov 20, 2007
I am using the following code to import a number of text documents into the same workbook, on seperate sheets and then to save the workbook as an excel file. the problem is that when the file tries to save (red section) I get a message telling me that Excel can not save the workbook in the requested format, I think because it is still trying to save them as text files rather than as an excel workbook. When I try to save manually, selecting .xls as the format it works fine, but I would really like this to be part of the macro to ensure the WB is saved.
Sub ImportFiles()
Dim Sfile As String
Dim count As Integer
Dim ans As String
Dim A As Integer
Dim FileSaveName As String
count = 0
Application.DisplayAlerts = False
Do
' Allow user to select the file to open, text files only
Sfile = Application. GetOpenFilename("Text Files(*.txt), *.txt")
' Check that a file has been selected
If Sfile <> "False" Then......................
View 4 Replies
View Related
Jan 24, 2008
I have a file with four sheets – File1, File2, Association and LookList. File1&2 are totalled, as is Association sheet but then are checked against each other using the totals. What I need to do is the following (but I am not sure how to do it using macro…)
Step 1 - ‘File1’ Sheet – It all comes in text format. Firstly, convert all of column B=>used range (it may vary to 40k records!) into numbers and total each column.
(I know the principal behind it – have a total variable for ColB, loop through and when it finds >0 then increment buy one and output under the last cell in that column – but I do not know the macro code)
Step 2 - ‘File2’ Sheet – Again, it's in text format. Firstly, convert all of column B=>used range into numbers and total each column(would contain same amount of records
as Step1!) .
Step 3 - ‘Association’ – same steps as for File 1 (again – same number of records).
Step 4 - THEN (and I have NO IDEA how to do this!...) I need to check the File1 & File2 against Association using the lookup and return the Serial number and the Association heading that does not match on a fourth sheet.
The problem is that – for example – Field-1 consists total of more than one column – contains 3 but could contain a total of up to 6 columns! - I understand excel formula ‘vlookup’ but am not sure how to do it when it is a combination of columns and even less when applying it to this! – In fact, for a macro I am not even sure what column format the lookup list should be!
View 9 Replies
View Related
Jan 28, 2010
I have a document passed to me that includes information pulled from an internal system.
I want to use some basic formulas to show Totals, averages etc.
However, the SUM function does not work on the data provided unless I select the cell (As if to edit the text) and the press enter.
Is there a quicker way of selecting all of the cells in Column A for example and performing this function on mass, rather than selecting each cell individually.
I have attahced a similar sample copy - My SUM formula is show in cell B15.
View 7 Replies
View Related
Jul 26, 2009
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
View 9 Replies
View Related
Oct 21, 2011
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
View 4 Replies
View Related
Nov 14, 2013
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
View 3 Replies
View Related
Feb 13, 2008
I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
View 2 Replies
View Related
Aug 9, 2008
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............
View 9 Replies
View Related
Jun 17, 2013
Imported contacts to appear in Autocomplete?
View 1 Replies
View Related
Jan 9, 2006
I am working with Microsoft Excel 2003. I am trying to import(or something
like it) from worksheet (A) to worksheet (B). Worksheet A is a spreadsheet
that I have saved to keep the same row names, etc. but the information within
the named cells is forever changing. I also save the information from
worksheet A, but have another copy that when I open, it always opens without
any changed data in it. MY question/problem is that I need to import the
data from A to B, and every time that A changes, I need B to automatically
update the data and continuously add to the spreadsheet I have made up for B.
I can NOT have A overwrite any data that I have already put into B.
View 13 Replies
View Related
Nov 7, 2006
I've imported a few thousand lines of data into excel, but some rows have a rogue character or a blank cell in the middle of them taking up a cell and shifting the rest of the row along one cell and putting it out of sync with the columns I want that data in.
Is there a way of remedying this without going through and manually moving a couple of hundred rows along one cell.
I'm not sure I've explained that particularly well, so I'll try and illustrate it with an example.
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB ------ 1.23 1.23 CCC 1.23 1.23
AAA 1.23 1.23 BBB 1.23 1.23 CCC 1.23 1.23
Ignore the fact that they are all 1.23, I've just used that to make it quicker for me to show. As you can see row 3 has a ------ (representing a rogue character or blank cell) in the 5th column shifting columns 5-9 right one cell.
Now as I said I have a few thousand lines of data like this with maybe 2-3 hundred rows with this problem and I really don't want to manually go through and fix each one.
Is there a tool in excel or a way of using VB to fix this?
Alternatively, is there a way when importing the data to have a number of delimiters rather than the one extra one you can select to remove these on importing?
Maybe it could be fixed by taking any blank cell (or character) and deleting it whilst shifting all cells to the right of it one cell to the left?
View 9 Replies
View Related
