You are here: Home / Archives for February 2009

Convert time in decimals to a standard time

February 28, 2009 By admin

If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:

TIME(hours,minutes,seconds)

Example:

We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:

=TIME(INT(A2);(A2-INT(A2))*60;0)

  • The first parameter in this formula,  hours is equal to the integer part of the decimal value: INT(A2).
  • The second parameter, minutes is equal to the fraction of the decimal value multiplied by 60:  (A2-INT(A2))*60.
  • The last parameter seconds, is set to 0 in our example.

Below are different examples based on this formula:

dtime1

Applies to Excel 2003

Filed Under: Date and Time Tagged With: Date and Time, Time

Create a hyperlink to an existing file or web page

February 27, 2009 By admin

To create a hyperlink to an existing file or web page, follow these steps:

  1. In the Insert menu click Hyperlink.
  2. The Insert Hyperlink window will open.

    3. hyperlink1

  3. Under Link to to the left side click Existing File or Web page.
  • To link to a file, Under Look in select Current Folder, then select your folder or file from the list to right.
  • To link to a file that you have recently used, click Recent Files and then click the file you want to link to, from the list to right.
  • To link to a web page, click Browsed Pages and then click the Web page you want to link to, from the list to right.

Applies to: Excel 2003

Filed Under: Links Tagged With: Excel help, Links

How to check spelling using the dictionary

February 21, 2009 By admin

Microsoft Excel cannot check spelling automatically as you type as you see in Microsoft Word for example. However you can check spelling all at once by selecting so from the Tools menu.

When you check spelling, Excel checks the entire active worksheet, including cell values, cell comments, embedded charts, text boxes, buttons, and headers and footers. However, Excel does not check protected worksheets, formulas, or text that results from a formula.

To check worksheet data for spelling errors, do one of the following:

1. Select the range you want to check:

  • To check the entire worksheet, click any cell.
  • To check a specific area of the worksheet, select the range of cells, rows, or columns that you want to check.
  • To check the contents of the formula bar only, activate the formula bar by pressing F2 (PF key 2).

2. On the Tools menu click Spelling.

3. When Excel finds a spelling error, it will display the Spelling dialog box. The suspected word will be shown and also a list of suggested words as an alternative to the word in error. You then have a number of options depending on which of these buttons you click:

spelling

  • Ignore Once: The current occurrence of the word will be ignored regarding spelling checking
  • Ignore All: All occurrences of the word will be ignored.
  • Add to Dictionary: The word will be added to the custom dictionary so that next time it will not be considered as an error.
  • Change: The word in error will be replaced with the suggested word you select from the list.
  • Change All: All occurrences of the word in error will be replaced with the suggested word you select from the list.
  • AutoCorrect: The word in error will be replaced with suggested word you select from the list. In addition to that, the two words will be added to the AutoCorrect list and from now own correction will be done automatically.

4. If you want to check the spelling of text in another language, in the Dictionary language box, click the language whose dictionary you want to use.

Applies to: Excel 2003

Filed Under: General, Validation Tagged With: Spelling, Validation

How to display leading zeros in a numeric value

February 17, 2009 By admin

If you want to display leading zeros in a numeric value, then  you can create a custom format for the relevant cell as follows:

1. Right-click on the cell where you want display leading zeros.
2. Select Format Cells.
3. When the dialogue box opens select the Number tab.
4. In the Category list to the left select Number. A default display will show on the sample area to the right.
5. In the Category list to the left select Custom. A list of custom formats will show to the right, and a default format will show in the Edit Box above.
7. Modify the format in the Edit Box by entering a number of zeros equal to the maximum number of  digits possible  in your value, and watch the sample.
8. When you are satisfied click OK.
9. The new format will be applied to the cell and will also be added to the list of custom formats.

In the example below, the maximum value expected is seven digits long. We entered the format as “0000000″, and because the actual value in the cell 5 digits “12345″, the display will show as “0012345″.

leadingzeros1

Applies to: Excel 2003

Filed Under: Formatting Tagged With: Formatting

How to find links in a workbook

February 13, 2009 By admin

There is no automatic way to find links used in a workbook. However links use brackets [ ] to enclose the source workbook name, and it also uses the exclamation mark at the end of the sheet name in the link. So you can search for one of these two symbols depending on what type of links you are looking for.

If you are looking for external links only, use the ‘[' . If you are looking for all links, external links and links within the same workbook, then use '!'.

Examples of links:

1. =Sheet2!$C$1
2. =[Book2]Sheet1!$C$1

Link 1 above is a link to a worksheet within the same workbook, while link 2 is a link to an external workbook.

Steps to find a link:

1. On the Edit menu, click Find. the Find and replace window will open.
2. Click Options to expand the window.
3. In the Find what box, enter “!”.
4. In the Within box, select Workbook.
5. In the Look In box, select Formulas.
6. Click Find All.
7. Your links will be listed in the box at the bottom of the Find and replace window.
8. To select the cell with a link, select the line that contains the link.

findlink1

Applies to Excel 2003

Filed Under: Links Tagged With: Find, Links, Workbook

Add hours or minutes to a standard time

February 12, 2009 By admin

If you have a standard time, current time for example, and you want to add to it hours, minutes or seconds to get to a new time, then what you need to do is to convert the hours or minutes to a standard time format using the TIME function. After that you can add the two values together using the “+” sign.

A generic formula will look like this:

New time = current time + Time(hours,minutes,seconds)

In the examples below we have a standard time (07:15) in column A.

1. In row 2 we are adding 3 hours (B2) to 07:15 (A2). The formula in D2 will be:  =A2+TIME(B2;C2;0). The new time is 10:15.

2. In row 3 we are adding 4 hours (B3) and 15 minutes (C3) to 07:15 (A3). The formula in D3 will be:  =A3+TIME(B3;C3;0). The new time is 11:30.

3. In row 4 we are adding 20 minutes (C4) to 07:15 (A4). The formula in D4 will be:  =A4+TIME(B4;C4;0). The new time is 07:35.

4. In row 5 we are adding 12 hours (B5) to 07:15 (A5). The formula in D5 will be:  =A5+TIME(B5;C5;0). The new time is 19:15.

5. The last example in row six is slightly different. The result of the addition is more than 24 hours. If we use a formula similar to the ones shown above the result will be 01:15 while the actual value is 25 hours and 15 minutes. I.e. anything more than 24 hours will be lost. To avoid this problem we modify the formula in cell D6 slightly to look like this:

=(A6+TIME(B6;0;0))*24

We will also change the format of cell D6 to number instead of time. The result of this formula is 25.25 hours(.25 to the right of the decimal point is a decimal value and not minutes).

Notes:

1. Cells that contain time (Columns A and D should be formatted as TIME, cells that contain Hours or minutes (columns B an C) should be formatted as NUMBER).

2. I did not include seconds in these examples. If you want to add seconds then you can specify it as the third parameter of the TIME function.

3. The examples use a 24 hour clock. You can use 12 hour clock with AM/PM without problems.

addtime4

Applies to Excel 2003

Filed Under: Date and Time Tagged With: Date and Time, Time

How to add or sum times

February 8, 2009 By admin

If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.

Example 1 : total hours less than 24.

Cells B1, B2, B3 and B4 contains the times.

Cell B5 (total) contains the formula:

=SUM(B1:B4)

The result of this sum is 23:05 (23 hours and 5 minutes).

Please note that cells B1to B5 are formatted as time (hh:mm).

addtime1

Example 2 – total hours greater than 24.

Cells B1, B2, B3 and B4 contains the times.

The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).

To avoid this problem we use the following formula instead: =SUM(B1:B4)*24

We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).

addtime2

Applies to Excel 2003

Filed Under: Date and Time Tagged With: Date and Time, Time

How to protect formula cells but allow access to data cells

February 5, 2009 By admin

By default all cells in a worksheet are locked (see image below). I.e when you protect a worksheet every cell in the worksheet will be protected.

protect1

To handle cell protection differently then you have change the default settings for the required cells.

Here is a scenario to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Uncheck the Locked and Hidden check boxes, then click OK.

These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Check the Locked and Hidden check boxes, then click OK.
  5. On the Tools menu click Protection and select  Protect sheet.

Applies to: Excel 2003

Filed Under: Data, Formulas, Security and Protection Tagged With: Cell Protection

How to extract text from another text string

February 1, 2009 By admin

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:
=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

Filed Under: Formulas, Functions Tagged With: Find, Formulas, Functions, String, Text