Forum Home
Press F1
Thread ID: 85002	2007-11-26 08:18:00	Converting data from a webpage	bpt2 (6653)	Press F1

Post ID	Timestamp	Content	User
615375	2007-11-26 08:18:00	I have downloaded a chart which includes time data in the form 23:45 (23 mins 45 seconds). I have copied it into an excel spreadsheet but it is not read correctly. How can I convert it into the form mm:ss?	bpt2 (6653)
615376	2007-11-26 20:25:00	Select cell(s) <format> <cells> <number> <Time> - and choose which format required.	hkeyzer (9827)
615377	2007-11-28 09:37:00	This does not seem to work. How can I convert 2:35 (2 mins 35 secs) to a decimal form that I can treat mathematically?	bpt2 (6653)
615378	2007-11-28 16:44:00	Copied from Excel Help' -------------------------------------------------------------------------- Convert times office.microsoft.com (javascript<b><('block');)Show All office.microsoft.com (javascript<b><('none');)Hide All Let's say you want to find out how many hours there are in five and a half days, or how many days there are in 3.45 years, or how many months have passed between a beginning date and an ending date. There are several ways to convert times. What do you want to do? Convert between time units (office.microsoft.com between time units) Convert hours from standard time format (hours : minutes : seconds) to a decimal number (office.microsoft.com hours from standard time format (hours : minutes : seconds) to a decimal number) Convert hours from decimal number to the standard time format (hours : minutes : seconds) (office.microsoft.com hours from decimal number to the standard time format (hours : minutes : seconds)) Convert between time units To do this task, use the CONVERT function. If the CONVERT function is not available, install and load the Analysis ToolPak add-in. office.microsoft.com (javascript<b><('divExpCollAsst_1')) How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program.Example The example may be easier to understand if you copy it to a blank worksheet. office.microsoft.com (javascript<b><('divExpCollAsst_2'))How to copy an example Create a blank workbook or worksheet. Select the example in the Help topic. Note Do not select the row or column headers. office.microsoft.com Selecting an example from Help Press CTRL+C. In the worksheet, select cell A1, and press CTRL+V. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.12AData6FormulaDescription (Result)=CONVERT(A2,"day","hr")Convert 6 days to hours (144)=CONVERT(A2,"hr","mn")Convert 6 hours to minutes (360)=CONVERT(A2,"yr", "day")Convert 6 years to days (2191.5)Note For conversions using a year, a year is treated as 365.25 days. Function details CONVERT (javascript<b><('/search/redir.aspx?AssetID=HP052090221033&CTT=5&Origin=HP030561101033')) office.microsoft.com of Page (office.microsoft.com) Convert hours from standard time format (hours : minutes : seconds) to a decimal number To do this task, use the INT function. Example The example may be easier to understand if you copy it to a blank worksheet. office.microsoft.com (javascript<b><('divExpCollAsst_3'))How to copy an example Create a blank workbook or worksheet. Select the example in the Help topic. Note Do not select the row or column headers. office.microsoft.com Selecting an example from Help Press CTRL+C. In the worksheet, select cell A1, and press CTRL+V. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.123ATime10:35 AM12:15 PMFormulaDescription (Result)=(A2-INT(A2))*24Number of hours since 12:00 AM (10.583333)=(A3-INT(A3))*24Number of hours since 12:00 AM (12.25)Function details INT (javascript<b><('/search/redir.aspx?AssetID=HP052091421033&CTT=5&Origin=HP030561101033')) office.microsoft.com of Page (office.microsoft.com) Convert hours from decimal number to the standard time format (hours : minutes : seconds) To do this task, use the divisor operator (/) and the TEXT function. Example The example may be easier to understand if you copy it to a blank worksheet. office.microsoft.com (javascript<b><('divExpCollAsst_4'))How to copy an example Create a blank workbook or worksheet. Select the example in the Help topic. Note Do not select the row or column headers. office.microsoft.com Selecting an example from Help Press CTRL+C. In the worksheet, select cell A1, and press CTRL+V. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.123AHours10.583312.25FormulaDescription (Result)=TEXT(A2/24, "h:mm")Hours since 12:00 AM (10:35)=TEXT(A3/24, "h:mm")Hours since 12:00 AM (12:15)Note You can also apply the time number format without using the TEXT function to specify the format. To view the number as a time, select the cell and click Cells on the Format menu. Click the Number tab, and then click Time in the Category box. However, if you use both a format applied with the TEXT function and number format, the TEXT function takes precedence. Function details TEXT (javascript<b><('/search/redir.aspx?AssetID=HP052093131033&CTT=5&Origin=HP030561101033')) office.microsoft.com of Page (office.microsoft.com) office.microsoft.com office.microsoft.com See Also (javascript<b><();) <LI class=cdOLstIN>Animations III: Timing (javascript<b><('office.microsoft.com)) <LI class=cdOLstIN>Free trial of the 2007 Microsoft Office system (javascript<b><('office.microsoft.com)) Make time count by using formulas (javascript<b><('office.microsoft.com)) if (typeof(FeedbackWizGenerateControl_Buttons) != 'undefined') FeedbackWizGenerateControl_Buttons('Was this information helpful?','Yes','No','I don\'t know','How was this information helpful? (optional)','How can we make this information more helpful? (optional)','What are you trying to do? (optional)','Back','Submit','Your feedback is being submitted.','We appreciate your feedback.','Do you need support, or do you have a suggestion about a product? {0}.','Contact us','office.microsoft.com my feedback','Although Microsoft cannot respond to every comment, we do use your feedback to improve the information we provide.','100%'); Was this information helpful? How was this information helpful? (optional) How can we make this information more helpful? (optional) What are you trying to do? (optional) Your feedback is being submitted. We appreciate your feedback. Change my feedback (javascript<b><(1, -1);) Although Microsoft cannot respond to every comment, we do use your feedback to improve the information we provide. Do you need support, or do you have a suggestion about a product? Contact us (javascript<b><('office.microsoft.com)). Contact Us (office.microsoft.com) Privacy Statement (r.office.microsoft.com) var gProductionDomains="office.microsoft.com";var gLinkT="assetid=xt";var gDomain="m.webtrends.com";var gTrackEvents=0;var wt_a=gProductionDomains.split(',');var wt_b=0;for(var i=0;i	Sweep (90)
615379	2007-11-29 16:43:00	I'm still at a loss. The time entries I copied from a website do not respond to formatting. They are in the form 2:24 (2mins 24 secs) but I cannot find a way to convert that time to a decimal equivalent, = 2.4.	bpt2 (6653)
615380	2007-11-29 17:07:00	I'm still at a loss. The time entries I copied from a website do not respond to formatting. They are in the form 2:24 (2mins 24 secs) but I cannot find a way to convert that time to a decimal equivalent, = 2.4. I am off to work shortly but will revisit this thread when I get home. Put the 2:24 in A1 then in A2 use a formula to convert A1 to decimal time.	Sweep (90)
1