PDA

View Full Version : [SOLVED] Weird Cell formatting



froggyface88
12-29-2013, 07:58 PM
Hi,


I'm in Excel 2003 and am trying to automate some fantasy basketball analysis. I am using the data query function to import a table from a website1 with the stats I need.


I want to sort by # of minutes, but when I import the data, the info in the minute column looks different in the cell than in the fx bar. For example, the cell displays "48:12:00" and the fx bar displays "1/2/1900 12:12:00 AM". Ultimately, I want that cell to read "48" (rounding the number of minutes played).


Any ideas on how to code the reformat of the cell to show me the number of minutes instead of some strange date format?

1: Note - this forum won't let me paste the website link. If you google "bastketballmonster" then click on "schedule" then "boxscores", you'll see the tables I mean.

Jan Karel Pieterse
12-30-2013, 02:07 AM
Can you post a workbook with just the sheet containing the web query?

westconn1
12-30-2013, 02:32 AM
there are several methods to convert the time value
the simplest is to convert the cell to text

range("f11").Value = "'" & range("f11").Text

as a numeric value a time is converted to a double value, added to date(0) 30/12/1899, hence to strange date in the editing bar, in the case of your example 2.00833333333333, 2 days and 12 minutes
you can return hours, minutes and seconds using time functions
?minute(2.0833333333333) =12
the hour function will only work below 24, but can be returned like
?(range("f11"))*24\1 + hour(range("f11")) = 48 where F11 contains the date value

unfortunately formatting the cell as time value does not help as excel still converts times to a date, but if you want to do calculations based on the time values, storing the values as text would require converting to some numeric form

froggyface88
01-01-2014, 09:38 AM
there are several methods to convert the time value
the simplest is to convert the cell to text

range("f11").Value = "'" & range("f11").Text

Doesn't do anything when I run it against the data.


as a numeric value a time is converted to a double value, added to date(0) 30/12/1899, hence to strange date in the editing bar, in the case of your example 2.00833333333333, 2 days and 12 minutes
you can return hours, minutes and seconds using time functions
?minute(2.0833333333333) =12
the hour function will only work below 24, but can be returned like
?(range("f11"))*24\1 + hour(range("f11")) = 48 where F11 contains the date value

I'm not sure I follow how to incorporate the minute function into the code?


Attached is a portion of the worksheet. All I want is to take the number in the "min" column and convert it into a number of minutes. I realize right now, the way the cells autopopulated from the table make the number look like hours instead of minutes. I don't care about the seconds value.

Thanks!

11018

SamT
01-01-2014, 10:09 AM
You need a helper column. I put this formula in column C and formatted C as Number with 0 decimal places. Note that it rounds off to the nearest minute.

=IF(B2<1,-100*MOD(B2,1),100*MOD(B2,1))

froggyface88
01-01-2014, 10:16 AM
11019

Thanks for the quick response SamT! Unfortunately, I'm still struggling. I added column C with the formula you suggested, but it isn't rounding correctly. I'm attaching the spreadsheet with the column. The correct numbers should read 35, 30,10, 24, 35, 34, 14, 1, 0, 16, 36

Paul_Hossler
01-01-2014, 12:10 PM
If you want to use a VBA user defined function



Option Explicit
Function GetMins(r As Range) As Long
GetMins = CLng(Split(r.Text, ":")(0))
End Function




Paul

SamT
01-01-2014, 12:33 PM
The display of the retrieved values is definitely confusing. It is an hh:mm:ss representation of a minutes value enter as a Date or Time. They should have been entered as
Range.Text = Left(CStr(Result of Minutes Retrieval), 2)

The correct formula is
=B2*24, but that still rounds the value to the nearest minute

If this is a problem use
=ROUNDDOWN((B2*24), 0)

froggyface88
01-01-2014, 01:42 PM
Thanks both SamT and Paul_Hossler - both of your solutions work great!

A second question. I've been using Data -> import external data -> new web query to get the data off the website into excel. The first time I tried this was at night, after all games had been played that day, and the web query worked fine.

I'm trying again today from scratch, but none of today's games have been played yet. I'm looking to analyze the data from yesterday's games, but when I go through the new web query interface and click back to yesterday, then select the tables I want to import, I get the following error message: "This web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar".

Is there a way to use the web query feature on previous calendar days of the data?

Thanks!

SamT
01-01-2014, 02:39 PM
FF88, please mark this thread as solved with the Thread Tools at the top and open a new thread with this last question.

Thanks,
Sam

froggyface88
01-01-2014, 03:09 PM
Thanks!