You are here: Home / Archives for December 2008

How to determine if a cell has a formula or not

December 30, 2008 By admin

If you want to determine whether a specific cell has a formula or a value then you need to check a range property called “HasFormula”. Here is an example where the border color of a cell is changed to red if it has a formula, otherwise it is set to black.

Dim myRange As String
myRange = “B5″
If ActiveSheet.Range(myRange).HasFormula Then
ActiveSheet.Range(myRange).Borders.Color = RGB(255, 0, 0)
Else
ActiveSheet.Range(myRange).Borders.Color = 0
End If

Suggested Post:
 How to write a VBA macro

Filed Under: Formulas, Visual Basic Tagged With: Excel Formulas

Calculate the number of months between two dates – knowledgebase article

December 27, 2008 By admin

This step-by-step article shows you how to calculate the number of months between any two dates.

To calculate the number of months between any two dates, use one of the following methods. Note that both methods use the following information:

• EDate = Earlier Date
• LDate = Later Date

NOTE: If you type LDate or EDate directly into this formula instead of into their cell references, you must surround it by quotation marks (for example, “9/7/00″).

Round Up

This method does not use the day of the month in its calculations. For example, given a start date of 10/31/00 and an end date of 11/2/00, one month is returned even though only two days elapsed.

For this method, use the following formula:

=(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell A2:

=(YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1)

The number that is returned equals the number of months from 10/31/99 to today’s date, rounded up to the next whole number.

Round Down

This method uses the number days in its calculations and rounds down to the nearest number of whole months. For example, given a start date of 10/30/00 and an end date of 11/30/00, one month is returned; however, an end date of 11/29/00 returns a zero.

For this method, use the following formula

=IF(DAY(LDate)>=DAY(EDate),0,-1)+YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell B1:

=IF(DAY(NOW())>=DAY(A1),0,-1)+(YEAR(NOW())-YEAR(A1)) *12+MONTH(NOW())-MONTH(A1)

NOTE: Ensure that cell B1 is formatted as General. The number returned equals the number of months from 10/31/99 to today’s date, rounded down to the nearest number of whole months.

Source: Microsoft Help and Support
Original article: http://support.microsoft.com/kb/214134

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

How to get the current date and time

December 13, 2008 By admin

If you want to get the current date and time in a cell then you can do one of the following:

1. To get the current date only use the TODAY() function.

Syntax

=TODAY()

2. To get the current date and time use the NOW() function.

Syntax

=NOW()

3. To get the current time only use the NOW() function, and then format the cell to display time only.

Note:

The TODAY() and NOW() functions will update the date and time whenever the worksheet is calculated. I.e. their values are variable.

To insert static date and/or time in a cell:

1. For current date: Select a cell, press CTRL then enter “;” (semicolon character).

2. For current time: Select a cell, press CTRL+SHIFT then enter “;”.

3. For current date and time: Select a cell, press CTRL then enter “;” to get the date. Enter SPACE, press CTRL+SHIFT then enter “;” to get the time.

You can also use Copy/Paste special. For example to To insert static date and time in a cell:

1. Enter “=NOW()” in a cell and hit enter. You will get the current date and time

2. Right click on the cell and select Copy.

3. Right click on the same cell and select Paste Special, then select Paste Values.

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

How to create a custom time format

December 4, 2008 By admin

Excel provides a vast number of common time formats. However if you do need a format that is not included in the built-in list then you can create your own custom format as follows:

  1. Right-click on the cell where the date is.
  2. Select Format Cells.
  3. When the dialogue box opens select the Number tab.
  4. In the Category list to the left select Time.
  5. In the Type list to the right select the time format closest to what you want. A sample format will show above.
  6. In the Category list to the left select Custom. A list of custom time formats will show to the right, and the format you selected will show in the Edit Box above.
  7. Modify the format in the Edit Box 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.

Applies to: Excel 2003

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

How to insert update date or timestamp in a cell

December 1, 2008 By admin

A few months ago a user asked this question in Excel Help Forum:

If you have two columns, is it possible to have column B put in the date that data was added to column A?

I have replied to the question in the forum at that time, and I have noticed since then that other users are asking the same question. For that reason I thought it might be a good idea to elaborate on the subject here.

To insert an update date or a timestamp in a cell, you need to use a worksheet event called “Worksheet Change”. Just follow these steps:

1. Switch to visual basic editor by clicking Tools –> Macro –> visual basic editor.

2. Click on the name of your sheet in project explorer at your left.

Timestamp1

3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.

4. Now you are in the Worksheet_Change event.

5. Copy this code between “Private Sub Worksheet_Change” and “End Sub”.

Col = Left(Target.Address, 2)
If Col = “$A” Then Target.Offset(0, 1) = Now

Timestamp2

Now whenever you change a cell in column “A” the next cell in column “B” will be updated with date and time.

You can change the code to suit your own needs, but the main idea is that you have to use the “Worksheet Change” event for this purpose.

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