Stop Excel From Converting Football Scores Into Dates
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
ADVERTISEMENT
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
Dec 6, 2009
I have a football pool I am doing with my family. I would like a macro that displays a message box that tells me the leaders of the pool using the grand total number. So in my attachment, the message box would say something like:
Sue is in first place with 12 points,
Bob and Dave are in second place with 9 points,
Larry is in third place with 3 points
It doesn't need to be exactly like that, but you get the gist of what I am looking for. The catch here is that the grand total row changes each week as I add games in, so the row moves down every week. I need the macro to stay with the grand total row from week to week.
View 4 Replies
View Related
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
Jul 4, 2014
How I can use a formula for inputting a persons position in a race for the scores appear automatically in the adjacent column. E.g 1st place 10 pts, 2nd 8pts etc?
View 3 Replies
View Related
Sep 15, 2009
I need to convert the following string into Excel dates:
Jan 1, 2009
Jan 12, 2009
Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
View 14 Replies
View Related
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
View Related
Oct 21, 2013
I have a list of dates and want to convert them to something like Monday 21st October instead of 21/10/13.
I am sure this is easy but cannot see how to do it. I am on Excel 13.
View 4 Replies
View Related
Oct 7, 2009
I'm opening a file in Excel, and it automaticaally changes a lot of the numbers to dates. How can I turn this off in the settings panel?
View 10 Replies
View Related
Jun 15, 2014
Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
View 5 Replies
View Related
Feb 19, 2008
I have a long column of dates YYYYmmdd and I need to be able to convert them to mm/dd/yy so that I can see (for example) 09/18/72 instead of 19720918
View 9 Replies
View Related
Apr 5, 2013
I have below format in excel and i will be updating scores for each parameter (column B).
I will be inserting a command/click button and when i click on the button macro should take the name, contact No., scores from column B and paste it in sheet 2 in the same workbook in one row, in the same way when I click the button the scores should be taken from sheet 1 column b and paste one below other row in sheet 2 each time i click on button.
Below is how raw data looks like.
Column A
Column B
Name:
ABC
Contact Number:
XYZ
Parameter
Score
[Code] ..........
Output expected in sheet 2 is as below.
Name:
Contact Number:
Parameter A
Parameter B
Parameter C
Parameter D
Total Score
ABC
XYZ
2
5
6
7
5
I am using Excel 2010.
View 5 Replies
View Related
Jun 5, 2014
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85
Cindy 50 60 100
Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
I am trying to do this in Excel 2003.
View 2 Replies
View Related
Aug 13, 2014
I have attached an Excel 2010 spreadsheet to show the question.
Essentially, I need to have a formula to indicate the numeric rank of each grade for each student.
I could sort the columns by NAME and SCORE.
However, I would prefer to have a formula that ranks each student's subject grades - some students have multiple numbers that are the same.
View 11 Replies
View Related
Nov 14, 2012
i have product group,product name and the statistics. I'll use an example of students with score. I have these set of data:
A B C D
1 Student Name Score Sum
2 Student1 Anna 48 80=Sumif(A:A,A2,C:C)
3 Student1 Anna 32 80
4 Student2 Tom 30 80
5 Student2 Tom 30 80
6 Student2 Tom 20 80
7 Student3 June 55 60
8 Student3 June 1 60
9 Student3 June 4 60
Now we have 2 students with tied highest scores with 80 Scores. Naturally i want the rank to be as follow: Top1 Anna since she has the highest score "48", and Top2 Tom, and Top3 June.
The problem is, the score data can vary and Top3 can probably have the highest score and he still didnt make the highest score collectively. And there will also going to be other situations as well because im working on a very large data set, and not these 3 students.
the max score is tied, and since im making another column with sum scores, the data is going to be redundant, and hopefully theres a formula to ignore this.
View 2 Replies
View Related
Oct 4, 2013
Excel 2003
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
View 9 Replies
View Related
Aug 20, 2014
Backstory: I am converting PDF documents to Excel files and some of the numbers are being transformed into a wonky date format which to the ***** eye looks semi correct but the underlying value in the formula bar is incorrect. Basically I need to copy and paste the value but when I paste special values it doesnt work. Below is the example
The cell value should be: 9856 When it is converted to excel the value shows 98 5 6 but in the formula bar it shows: 5/6/2098 The cell is formated as: Custom yy m d;@
If I get the value in the cell to read 98 5 6 I can use trim or something to get it down to 9856 but the issue is when I use those functions they return 72446. I guessing this is because they read the formula value not the cell value.
I need to somehow get the value in the formula bar to read 9856.
View 3 Replies
View Related
Jun 27, 2013
Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies
View Related
Jan 12, 2006
I have dates being imported (AS TEXT) to excel in various formats and want them all to appear in their cells in the same 8 digit format (MMDDYYYY) so it can be exported to another program
for example:
Single Digit Month and Day
cell A1: 3179 (convert to 03011979)
cell A2: 30179 (convert to 03011979)
cell A3: 030179 (convert to 03011979)
cell A4: 311979 (convert to 03011979)
cell A5: 3011979 (convert to 03011979)
cell A6: 03011979 (already correct)
Single Digit Month and Double Digit Day
cell A7: 52165 (convert to 05211965)
cell A8: 052165 (convert to 05211965)
cell A9: 5211965 (convert to 05211965)
cell A10: 05211965 (already correct)
Double Digit Month and Single Digit Day
cell A11: 12523 (convert to 12051923)
cell A12: 120523 (convert to 12051923)
cell A13: 1251923 (convert to 12051923)
cell A14: 12051923 (already correct)
Double Digit Month and Double Digit Day
cell A15: 112180 (convert to 11211980)
cell A16: 11211980 (already correct)
View 14 Replies
View Related
Jul 20, 2006
I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
View 10 Replies
View Related
Jul 15, 2008
converting text strings to dates.
The dates I have imported into a spreadsheet are of the format:
11.05.08
I use code to convert it to this format
11/5/08
But with some of the dates, I get
11/5/2008 whereas with others, I get
11/5/08
However, if I recorded a macro the date format appears as
11/5/2008 for all the dates.
But the code is excatly the same, if I
see below:
My
For Each celld In Range([e1], [a1].End(xlToRight))
If celld.Value Like "*DATE" Then
celld.EntireColumn.Select
On Error Resume Next
Also, how do you convert strings to dates using VBA?
I thought DATEVALUE performed this function but keep getting errors when i try using it.
View 9 Replies
View Related
Feb 13, 2007
I'm a moderate user of excel with some experience but need help creating a specific if statment.
I have a column "A" that shows dates formated like this: 060115 (January 15, 2006). Because excel does not recognize this as a valid numerical date I need column B to show what year the date falls under. To complicate it I need it to reflect a seasonal year from April 1 YYYY, to March 31, YYYY.
So here's an example. Say I have a date in column "A" of 050612 (June 12 2005). I need column "B" to look at that date and determine if it's >040105 and <033106 and return a result of "2005". I then need the formula to calculate an answer for up to 5 years worth of returns.
how to set up this type of =IF statement?
View 9 Replies
View Related
Sep 8, 2009
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
View 3 Replies
View Related
Oct 4, 2007
I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
View 9 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
Jan 4, 2014
I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
View 4 Replies
View Related
May 8, 2012
i'd like a formula to change a date to month and year
Column E
11 May 201123 November 201108 July 201103 September 201111 March 201224 December 2011
I've used =Text(e1,"MMM") to pull the month through but would like to include the year too.
View 3 Replies
View Related
Jan 27, 2007
I get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
View 9 Replies
View Related
Aug 17, 2009
There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.
=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))
But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?
View 5 Replies
View Related
Feb 13, 2008
I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.
View 2 Replies
View Related
