Anybody know how to format excel for time?

Triathlon Forum
1

building a training log using excel and can’t seem to format times to not be time of day. i’d like to put in splits like 7:40, 7:42, … and have them as stand alone numbers so i can add and average them. they always come out as 7:42am…

anybody know if this is possilbe? how to do it? or do i have to change it to a fraction.

thanks

2

Convert times http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033Show All http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050011033Hide All

http://office.microsoft.com/global/images/bluedrop.gifConvert between time units

Use the CONVERT function to do this task.

If this function is not available, install and load the Analysis ToolPak add-in.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033 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. Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033How? Create a blank workbook or worksheet. Select the example in the Help topic. Do not select the row or column headers.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA060481921033

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. 1 2 A Data 6 Formula Description (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

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033Convert hours from standard time format (hours : minutes : seconds) to a decimal number

Use INT function to do this task. Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033How? Create a blank workbook or worksheet. Select the example in the Help topic. Do not select the row or column headers.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA060481921033

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. 1 2 3 A Time 10:35 AM 12:15 PM Formula Description (Result) =(A2-INT(A2))*24 Number of hours since 12:00 AM (10.583333) =(A3-INT(A3))*24 Number of hours since 12:00 AM (12.25) Function details

INT

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033Convert hours from decimal number to the standard time format (hours : minutes : seconds)

Use the divisor operator (/) and the TEXT function to do this task. Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA790050001033 How? Create a blank workbook or worksheet. Select the example in the Help topic. Do not select the row or column headers.

http://office.microsoft.com/global/images/default.aspx?AssetID=ZA060481921033

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. 1 2 3 A Hours 10.5833 12.25 Formula Description (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

3

What he said.

4

select the cell, right click, select Format Cells then Time then 13:30:55

This gives you 24 hour time, hh:mm:ss

Record10 is giving you the nuclear option - which I don’t think you need at the moment…

5

Much easier answer than mine -
.

6

Holy shit. Can’t tickle yourself? I’m surprised you can walk/talk and match your socks. Wally would be proud.

7

Thanks all
.