You are here: Home / Archives for January 2009

How to insert current date in worksheet footer

January 27, 2009 By admin

To insert the current date in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert the current date:

1. Click on the section where you want the date to be inserted.
2. Click the Date icon footerdate.
3. The following code will be inserted in the selected footer section: “&[Date]“.
4. Click OK and you will see the current date in the preview window.
5. Click OK again to finish the dialog.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.
3. You can use a similar procedure to insert the date in the header if you want to.

Applies to: Excel 2003

Filed Under: Customization, Date and Time, Formatting, Printing Tagged With: Customization, Date, Excel, Footer, Formatting, Header, Printing

Calculate the difference between two times

January 26, 2009 By admin

The way you calculate the difference between two times depends on the way you want to present your results:

1. Present the result in standard time format (hh:mm:ss).

Simply subtract the two times from each other and format the result cell as time.

Examples:

A1 = 9:15:00
B1 = 14:07:20
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

or if you are using a 12 hour clock then the same example will be like this:

A1 = 9:15:00 AM
B1 = 02:07:20 PM
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

If you want the difference in hours and minutes or even in hours only, then just customize the cell format for that purpose.

Notes:

When you use this option then:

1. Your result should always be positive. I.e. the time in B1 must be later than the time in A1.

2. Hours will not exceed 24 and minutes  or seconds will not exceed 60.

For more help on time formatting check this post:

How to create a custom time format

2. Present the result as a total of hours, minutes or seconds:

Example:

A3 = 1/25/2009  21:03

B3 = 1/26/2009  23:17

1. To get the total hours between the two times use the formula:

=INT((B3-A3)*24). The result is 26.

2. To get the total minutes between the two times use the formula:

=(B3-A3)*1440. The result is 1574.

3. To get the total seconds between the two times use the formula:

=(B3-A3)*86400. The result is 94462.

Notes:

1. In all three cases (B3-A3) will give the number of days. Then you multiply by the apprpriate constant to get either hours, minutes or seconds.

2. INT function is used in the first case to get whole number. If you are interested in the fraction then you can ignore the INT function.

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

Nesting the IF function

January 22, 2009 By admin

The IF function is used to conduct conditional tests on values and formulas, and it is syntax is;

IF(logical_test,value_if_true,value_if_false)

As you can see it has only one logical test, and then the cell which has the formula will have a certain value if the test is “TRUE” and another value if the test is “FALSE”.

If you have more than one logical test, then you can nest more than one IF function within each other.

In the example below we have a list of hotel ratings and room prices. We want to check:

1. The rating of the hotel. If it is less than 4 stars then the cell value will be “NOT OK” and the test will end there.

2. If the rating is 4 stars or more then we will conduct another test on the room price.

nestedif

A generic formula for cell “C2″ for our case will be something like this:

=IF(A2<4;”NOT OK”;(Another test))

In place of “Another test” we will have the formula:

IF(B2>80;”NOT OK”;”OK”)

So joining these two formulas together we will have the final formula (in C2):

=IF(A2<4;”NOT OK”;(IF(B2>80;”NOT OK”;”OK”)))

Filed Under: Formulas, Functions Tagged With: Excel Formulas, Excel Functions, Nesting

How to view hidden sheets in a workbook

January 19, 2009 By admin

To view hidden sheets in a workbook, you need to unhide them first.

To unhide a worksheet:

1. In the Format menu point to Sheet then click Unhide.

unhidesheet1

2. The Unhide window will open, with the hidden sheets listed.

3. Select the sheet you want to unhide then click OK.

unhidesheet2

Applies to: Excel 2003


Filed Under: Formatting, General Tagged With: Unhide, Worksheet

Copy and paste data as a picture (image)

January 17, 2009 By admin

To copy and paste data as a picture (image) follow these steps:

1. Select the range you want to copy.
2. While holding down the SHIFT key, Click on the Edit menu.
3. Click Copy Picture.
4. Select a cell in the range where you want to paste the picture.
5. Click Paste Picture in the Edit menu ( while holding down the SHIFT key).

Copy Picture

Applies to: Excel 2003

Filed Under: Data, General Tagged With: Copy Picture, Paste Picture

How to insert page number in worksheet footer

January 13, 2009 By admin

To insert page number  in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert a page number:

1. Click on the section where you want the page number to be inserted.
2. Click the Page Number icon pagenos.
3. The following code will be inserted in the selected footer section: “&[Page]“.
4. Click OK and you will see the page number in the preview window.

If you want to add the number of pages so that your footer looks like this: “Page 1 of 5″ then:

1. In the selected section type “Page “.
2. Click the Page Number icon to get the code “&[Page]” inserted.
3. Immediately after this code type ” of  “.
4. Click the Number of Pages iconNoOfPages.
5. Your final code will look like this: “Page &[Page] of &[Pages]”
6. Click OK.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.

Applies to: Excel 2003

Filed Under: Formatting, Printing Tagged With: Footer, Formatting, Printing

How to calculate the number of days between two dates

January 12, 2009 By admin

To calculate the difference or the number of days between two dates, you simply subtract one date from the other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:

=B1 – A1

This should give a result of 51 ( 5 days in Nov + 31 in Dec + 15 in Jan).

Note that the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time Tagged With: Dates

How to extract day, month or year from a date

January 8, 2009 By admin

To extract day, month or year from a date, you use the corresponding Excel built in function for each one of these date parts.

  • Day: Day(mydate).
  • Month: Month(mydate).
  • Year: Year(mydate).

Examples:

Suppose you have in cell A1 (formatted as date), the date:15/11/2009 (ddmmyyyy), Then:

  • Day(A1) will result in 15
  • Month(A1) will result in 11
  • Year(A1) will result in 2009

Applies to: Excel 2003

Filed Under: Date and Time, Functions Tagged With: Date, Day, Month, Year

How to stop printing blank pages

January 6, 2009 By admin

If you are getting blank pages when you are printing a worksheet, then there are a number of possibilities:

1. You have some ‘unnoticed’ text in one of the cells, e.g. ‘.’ or ‘,’.

2. You have cells that contain text, but the text color is the same as the cell background color.

3. You have a cell with a formula that returns a blank value.

4. You have empty cells that are formatted with borders or shading.

5. You have set a print area that spans into more than one page.

Possible Resolutions:

1. For unnoticed text, press CTRL+END to find the last non-blank character, and delete it if it is not required. Repeat the process if necessary.

2. For formulas, display formulas by pressing CTRL+’~’ then try to find the formula that returns a blank value.

3. Set the print area to the required cells only. Read more.

Filed Under: Printing Tagged With: Blank pages, Print area, Printing

How to clear conditional formatting of a cell

January 5, 2009 By admin

  1. Select the cell(s) for which you want to clear conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. The Conditional Formatting dialogue will open, and the applied conditions will be listed.

    4. Conditional formatting

  4. Click Delete.
  5. The Delete Conditional Format window will open.

    6. Delete format

  6. Select the condition(s) to delete then press OK.

Applies to: Excel 2003

Filed Under: Data, Formatting Tagged With: Clear, Conditional Formating, Delete
« Older Posts