Trouble with time formats

```Any help appreciated!

I need to work out the average speed it would take to cover a certain
distance.

EG.

If you covered 10 miles in 28 minutes what would teh average speed be...

Column A:    10 miles
Column B:    28 minutes
Column C:    Would return 21.43mph.

I then want to to add time increments of say 15 seconds in rows so that I
can see that if it took 28 minutes, 45 seconds, the average speed column
would return 20.87mph.

I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
but I want to format it as time, i.e: 00:28:45 (or 01:10:30)

Is this possible?

Mark

```
 0
mambros2 (3)
7/10/2003 2:43:13 PM
excel.misc 78881 articles. 5 followers.

2 Replies
505 Views

Similar Articles

[PageSpeed] 8

```XL stores times as fractional days (e.g. 3:00:00 = 0.125), so to get
hours, you need to multiply by 24:

A1:     10
B1:     00:28:00
C1:     =A\$1/(B1*24)

then

B2:     =B1 + TIME(0,0,15)
C2:     =A\$1/(B2*24)

Copy B2 and C2 down as far as desired.

In article <beju21\$k973@eccws12.dearborn.ford.com>,
"Mark Ambrose" <mambros2@ford.com> wrote:

> Any help appreciated!
>
> I need to work out the average speed it would take to cover a certain
> distance.
>
> EG.
>
> If you covered 10 miles in 28 minutes what would teh average speed be...
>
> Column A:    10 miles
> Column B:    28 minutes
> Column C:    Would return 21.43mph.
>
> I then want to to add time increments of say 15 seconds in rows so that I
> can see that if it took 28 minutes, 45 seconds, the average speed column
> would return 20.87mph.
>
> I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
> but I want to format it as time, i.e: 00:28:45 (or 01:10:30)
>
> Is this possible?
>
>
> Mark
>
>
```
 0
jemcgimpsey (6723)
7/11/2003 1:17:04 AM
```"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-579690.19170410072003@msnews.microsoft.com...
> XL stores times as fractional days (e.g. 3:00:00 = 0.125), so to get
> hours, you need to multiply by 24:
>
> A1:     10
> B1:     00:28:00
> C1:     =A\$1/(B1*24)
>
> then
>
> B2:     =B1 + TIME(0,0,15)
> C2:     =A\$1/(B2*24)
>
> Copy B2 and C2 down as far as desired.

Excellent work, thank you.

Mark

```
 0
7/14/2003 5:35:49 PM

Similar Artilces:

Plot time scale in Excel
I need help on plotting time in x axis with 15 minute intervals that will roll over to the next day i.e midnight roll over. Need this very urgently... Shuvro, Generally, per Excel’s help you can’t create time-scale charts from data that is measured in intervals of hours, minutes, or seconds. Only days, months, and years are considered base units in time-scale charts. To get around this limitation, assume you have the following time data in cells A1:A11 and plotted data in cells B1:B11: Col A is Time Format Data 05/03/2005 23:00 6 05/03/2005 23:15 4 05/03/2005 23:30 5 0...

Trouble importing Quicken 2004 data
I have used Money for years and am very used to it. I got married this year and my new wife is used to using Quicken so I have all this year's data in Quicken 2004. I thought I would see if I could transfer data back into Money, as there are various aspects of Quicken that I don't like - having got used to Money's way of doing it and which I prefer. I downloaded the trial version of Money 2004 Deluxe and when I import data, I get all the accounts imported, but the entries are empty. Has anyone any suggestions as to what I am either doing wrong or I can do to correct this ...

HTML format
I am using POP yahoo and sending an email from my Outlook2003 to my hotmail/Gmail/Yahoo account in HTML format. When I receive the email at my hotmail/gmail/yahoo and view that email in browser I can see that each line has one or two extra line breaks. I diid not compose the email like that. When I want to make a paragraph I just enter "Enter Key" twice. But when I receive there are 4 or more line breaks after each paragraph. Is there some setting in outlook that I need to pay attention. Why the format are not retained. Thanks "abcd" <abcd@abcd.com> wrote i...

file format not valid
When opening a spreadsheet, message xxx.xls File Format is not valid and spreadsheet will not open. If you go into word and show all files and open the spreadsheet it opens. How can I get it back to open in excel the way it should be instead of word? "KJ" wrote > When opening a spreadsheet, message xxx.xls File Format > is not valid and spreadsheet will not open. If you go > into word and show all files and open the spreadsheet it > opens. How can I get it back to open in excel the way it > should be instead of word? When you open it in Word does it prompt y...

how do I enter data from 1 spreadsheet to another many times
I have on one page the basic data in singular line form, To translate this to a more readable form I made another page in the folder into a more readable form of which I can print and use away from the computer. I would like to know how can I use the form to copy the required data without the labor intensive re-entering in each form of a new form for each line in the oringinal data sheet. is there a formula or set of instructions that will do this automatically copying the desired cell onto the new page without all of the work? I hope I have expalined myself claearly since I'm a nov...

Simple Conditional Format with more than 3 options
Is there a simple way to extend conditional formatting to cover more than 3 conditions? thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Upgrade to xl2007. Or use an event macro that replaces the conditional formatting??? "Lionel B. Dyck" wrote: > > Is there a simple way to extend conditional formatting to cover more than 3 > conditions? > > thanks > -- > Lionel B. Dyck <>< > AIM ID: lbdyck Yahoo IM: lbdyck > Homepage http://www.lbdsoftware.c...

Excel formatting #3
Could someone please help with a macro or any other suggestion to format an Excel sheet. Basically data is downloaded from a bank every week and the size of the file can vary but the formatting is constant. That is each record is 7 lines. So first record is line1-7, second record is line 8-14, third record is line 15-21 and so on. I need to format this data and basically I can record a macro for one data set that is for the record on line 1 to 7. My question is how can I set it to replicate so that it replicates for line 8-14 and then 15-21 and so on. The source formatting remains the same...

Copying Excel page formatting to other pages
I'm a new user of Microsoft Office 2003 and am having trouble finding help information on how to copy Excel formatted sheets (pages) to other sheets to ensure the formatting is the same on additional sheets (pages). One way is to just CTRL-drag the sheet - this will create a copy of the original sheet (together with all formats and column widths), but, of course, will also include any data or formulae. Another way is to highlight all the cells in the first worksheet (click the intersection of the row and column identifiers) and click <copy>, then click the second worksheet tab, e...

Conditional Formatting (Date vs Number)
I have a spreadsheet that is calculating intervals for equipment maintenance. Some of the equipment must be serviced every x months, some of the equipment must be serviced every x hours (of run time). I have all the calculations worked out. The problem I am having is with conditional formatting. In column A I have text that says either "HOURS" or "MONTHS". Based on the value of that cell, I need Column B to be formatted as either a Date (to return the date of the next service) or as a Number (to return the hours till next service). Any ideas on how to do that? Conditional...

Time Picker
Just as Stephen Lebans has one for the date picker, is there one that's a time picker and avoids the OCX versioning issues that I can just incorporate into my modules? That would be one less thing I would have to install on each and every single PC using Office XP. If not, I have a couple of other ideas as to how to go about this as I'm attempting to keep deployment relatively simple. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 ...

different start time different days
How can I set different start times for different days? Outlook seems to assume I work every day for the same hours, not so. I was hoping to use its shading as a visual guide for different days, but it wants all days to have the same time schedule. -- qpackard http://forums.slipstick.com Outlook doesn't handle shift work. You could make tentative appointments for the work hours to highlight them. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: ...

Problem with custom format
Should be simple solution but I can't figure it out. I want a user to b able to enter a 16 digit number in a cell and have the cell display a follows. 1234-1234-1234-1234 I applied this custom format to the cell ####-####-####-#### When the number is entered as 1234123412341234. The cell display 1234-1234-1234-1230. The last digit is changed from a 4 to a zero What's going on?? It doesn't matter what sequence of 16 digit number you enter, the last digit is always changed to a zero. By the way th entry doesn't have to be treated as numeric it could be text also. Any help wou...

Find cells with conditional formats
Is there an easier way to find the cells with conditional formats than going to each cell and selecting format, conditional formatting? On Aug 28, 9:37=A0pm, mjones <mich...@quality-computing.com> wrote: > Is there an easier way to find the cells with conditional formats than > going to each cell and selecting format, conditional formatting? Found it. It's under Edit, Go To, Special. Thanks. My free "Formats & Styles" Excel add-in provides a count of CF cells that includes a count of cells with multiple conditions. Download from... http://excelusergroup.org/m...

CRM Time Stamp issue
When I check the properties of any of my activity records, the time stamps are incorrect, my server time settings are set to GMT, as are the crm system settings. If I create two tasks one after the other the time stamps could be an hour apart. Any ideas? ...

email in outlook keeps changing receive date and time
I don't know if this is an exchange or outlook problem. but my email receive date and time, and whether marked as unread constantly updates. I read the emails then some time later look at inbox and all the emails have new current date and time and are marked unread. how odd I'd appreaciate any help Jon On Wed, 12 Apr 2006 11:33:38 -0700, "jon straub" <jon@jonstraub.com> wrote: >I don't know if this is an exchange or outlook problem. but my email >receive date and time, and whether marked as unread constantly updates. I >read the emails then...

%s in Accounting Format
I am using Excel 2007. I want to have a column that lists percentages - but to still be able to use the accounting single underline correctly. When I manipulate the accounting format to _(*#,##0.00%_) the underlining is not correct. If I use the accounting format I cannot get "%" signs. Thanks. Does this work? _(* 0.00%_);_(* (0.00%) -- Regards, Peo Sjoblom "jadeB" <jbschipper@gmail.com> wrote in message news:a9dfe12b-3c2f-4302-ba49-2c2dd39240c2@33g2000yqm.googlegroups.com... >I am using Excel 2007. > > I want to have a column that lists percen...

combine hide row macro with conditional formatting
I'm sure one of you gurus can solve this in a flash. I've recorded a simple macro to hide a row (Alt O, Alt R, Alt H) but I want it to run automatically based on a cell value. For example: If cell value "A1" is "x" then I want the macro to run, and repeat itself down the column "A2, A3" etcetera until it reaches a blank cell. Is there a way to plug my existing macro "hiderow" into Conditional Formatting for a column; or is there a simpler solution? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.c...

Excel Time Conversion
I have a time field that displays data such as 0:35, 25:42:00, 16:00, etc. which is the total hours for a category. My goal is to add all these up to get a total of these hours by category. When you click on a cell, the formula bar shows things like 1/1/1900 1:42:00 AM instead of 25:42:00. Can you tell me how to convert the data to actual hh:mm:ss so I can add them up? Many thanks! Hi It's a formatting issue, the calculation is fine. See if you have a number format in Time looking like 37:30:55 -use that if so. If not then create a custom numberformat [h]:mm:ss HTH. Best wishes...

Trouble with getting a distinct count
I am having a lot of trouble figuring this out, I am embarassed to say, and was hoping one of you kind folks could steer me in the right direction. I have a table with the following fields: CustomerID (a unique number), ServiceID(a unique Number for each service delivered), ServiceTypeID (A unique ID number assigned to each service), ServiceName, and ServiceDate. Every time a customer receives one of several services it is recorded in the table. A customer can receive any of the services numerous times or not at all. What I can't figure out how to do is to come up with a distinct coun...

How do I set up a stacked bar chart using military times?
When my values are time, whenther they are reagular or military, the bottom bar is correct, but the others are not. As anexample, if I want a stacked bar that shows the following 3 times: start work 8:00 lunch 12:30 clock out 16:30 how do I get the bars displayed correctly as well as the "displayed values"? I have just put up a proposed solution for you at:- www.pierrefondes.com - first (top) example on the home page. If my comments have helped please hit Yes. Thanks! "Healthcare Quality Analyst" wrote: > When my values are time, whenther they ar...

Trouble after Removing Xp Guardian Hoax
Microsoft Essentials after an update apparently did remove the XPGuardian hoax/infection. However there seems to be some damage left behind..I cannot access many of my programs like I used to. eg clicking on ADD OR REMOVE PROGRAMs I get this center screen saying... C:\Windows\System32\rundll\\32.exe Application Not Found. Other programs like Word and Access. message appears App NoT Found. Other programs ask What program do you want to open this file and give 15 options..have tried a couple dont know which to click on ...to no avail. eg. my canon zoombrowser wont open . ...

run-time error 8007007e 8.0 support administrator's console
Every time I try to run it I get this error. Works fine on other computers in the area. Using XP Pro, SP2. HELP ...

Restrict HTML Formatted Mail
IS there a way to force all your incoming mail to be plain text? I do not want incoming mail to be in HTML format. I would like to restrict incoming mail or convert it to plain text once received (automatically). In Outlook 2003: Menu Tools/Options, Tab: Preferences, Button: Email Options, check: Read all standard mail in plain text. If you have different Outlook, check its help file. Hope this is useful to you. Let us know. rms JImL wrote: > IS there a way to force all your incoming mail to be > plain text? I do not want incoming mail to be in HTML > format. > ...

differences in compile time environment
hi, i have a visual c++ service project, and i'm experiencing problems related to netwrok functions. i understand that there should be a difference in the executable if I compile with visual c++6(sp5) or visual studio 2003. but should there be a difference if i compile my project on windows 2000 or windows 2003? please supply information from your experience regarding differences you encountered related to compiling on the 2 platforms. TIA. Hallo z f! > i have a visual c++ service project, and i'm experiencing problems related > to netwrok functions. > i understand tha...

Conditional Formating with a formula
I have a cell that has a formula to VLOOKUP another sheet. I want to conditional format the cell to turn yellow when no value is returned. If the VLOOKUP has no value returned on the cell #N/A appears. I tried to say in the conditional format when cell = #N/A turn yellow but it does not work I am thinking it doesn't work because of it being a formual and #N/A is not a true returned value. Thanks You can use the ISNA() function to test for that. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "ssciarrino" <...