# Thread: Date Formula (Excel 2K)

1. ## Date Formula (Excel 2K)

Good Morning,

What I'm trying to accomplish, w/o putting the formula in a cell, is provide the persons age based on their birthdate.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wshSource As Worksheet
Dim birRange As Range, ageRange As Range
Dim ageInt As Integer

Set wshSource = Worksheets("Identify")
Set birRange = wshSource.Range("E:E")
Set ageRange = wshSource.Range("F:F")
ageVar = DateDiff(birRange, Now(), "y")

If Not Intersect(Target, birRange) Is Nothing Then
If Intersect(Target, birRange).Cells(1) <> "" Then
ageRange = ageVar
End If
End If

But I get an error msg "type mis match"...any ideas what I'm doing wrong this time?

2. ## Re: Date Formula (Excel 2K)

If you get an error message, it is useful to mention which line is highlighted, so that wer odn't have to guess.

The immediate problem is that the arguments to DateDiff are in the wrong order: the syntax is DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]), for example DateDiff("d", Now, TheDate) (this is from the online help)

But repairing that wouldn't help much; the logic for your code is unclear. You cannot calculate an age for an entire column in one variable. Formulas would be much easier here. Why do it the hard way?

3. ## Re: Date Formula (Excel 2K)

Hans,

So sorry for omitting where the error msg is coming from.

It's coming from this line: ageVar = DateDiff(birRange, Now(), "y")

I didn't think of it in terms you are stating...rather as the user inputs a birthdate then the age would be calculated in the column respective to the entry.

The reason, I suppose, as you say, for doing it the hard way, is because I can't protect the column (therefore I can't protect the formula). I have other codes running that won't work if the worksheet is protected.

4. ## Re: Date Formula (Excel 2K)

You don't need the SelectionChange event, but the Change event, and you need quite different code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim intAge As Integer

If Not Intersect(Target, Range("E:E")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("E:E"))
If IsDate(oCell) Then
intAge = Year(Date) - Year(oCell)
If Month(Date) < Month(oCell) Or _
(Month(Date) = Month(oCell) And Day(Date) < Day(oCell)) Then
intAge = intAge - 1
End If
oCell.Offset(0, 1) = intAge
Else
oCell.Offset(0, 1) = ""
End If
Next oCell
End If

Application.EnableEvents = True
Set oCell = Nothing
End Sub

5. ## Re: Date Formula (Excel 2K)

As always Hans, you are greatly appreciated. It seems as someone would enter data for the birthdate the formula would work w/o any problem but as the user moved forward and make a mistake they would inevidabley delete the formula as well as the AGE and of course complain the spreadsheet wasn't working. I had to find a way to prevent this from happening.

As I usually do, fumble my way around, tried to figured it out for myself....but it's nothing like Access...and I'm forever lost....so when I say ... "you are greatly appreciated"...it's genuinly meant.

6. ## Re: Date Formula (Excel 2K)

1- You are using the wrong event to accomplish what you are trying to do. In the Worksheet_SelectionChange event routine, the Target parameter is the cell range of the cell that the selection is changing to. Therefore, you can not use it to determine if the cell that was changed contained a birth date. You want to use the Worksheet_Change event routine. In that routine, the Target parameter points to the cell that was just changed.

2- Your routine will probably not do what you want if the user clears the birth date cell. In that case, it will leave the previously calculated age in the age cell.

3- Your routine will not work correctly if the user pastes ages into more than one cell. It will only calculate the age for the selected cell in the paste range. This will either leave the age cells for the other cells in the paste range blank or containing an incorrect previously calculated range.

4- There should be no reason that you can not use a formula on the worksheet to calculate the age, and then protect the worksheet with the cells containing the formula being protected. This is exactly what worksheet protection is for.

If you must use an event routine to do this, then you need something like this:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("E:E"))
If oCell.Value = "" Then
Range("F1").Offset(oCell.Row - 1).Clear
Else
Range("F1").Offset(oCell.Row - 1).Value = DateDiff("yyyy", oCell.Value, Now())
End If
Next oCell
Application.EnableEvents = True
End If
End Sub
</pre>

7. ## Re: Date Formula (Excel 2K)

Thank you Legare,

As I mentioned previously, it wasn't the formula that wasn't working (unless of course it was deleted in error by the user) it is other codes that won't work if the worksheet is protected. However all is well now. Thanks for your suggestion.

8. ## Re: Date Formula (Excel 2K)

I'm not sure what "it is other codes" means. If that is VBA code, it should be possible to make it work if the worksheet is protected. The code can unprotect the worksheet, do whatever it needs to do, then re-protect the worksheet. If "other codes" means something else, could you explain?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•