Calculating Age In VBA (Chip Pearson)
 

I am not understanding something of Chips function.
If I run a macro with "MsgBox Age(12 / 3 / 61, 1 / 31 / 8)" I get "0 years 0
months 0 days" returned? Obviously there is at least 46 years here. What do
I have formatted wrong?

'======author Chip pearson
Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

Calculating Age In VBA (Chip Pearson)
 

Maybe

=Age(E1,TODAY())

Where E1 is the DOB

Mike

"Rick S." wrote:

I am not understanding something of Chips function.
If I run a macro with "MsgBox Age(12 / 3 / 61, 1 / 31 / 8)" I get "0 years 0
months 0 days" returned? Obviously there is at least 46 years here. What do
I have formatted wrong?

'======author Chip pearson
Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

Calculating Age In VBA (Chip Pearson)
 

ActiveUser.BrainCells.Add(12HundredBazillion)
Yea, thats better.

Works fine with variables.

'======
Dim x As Date
Dim y As Date
x = InputBox("Enter Date 1")
y = InputBox("Enter Date 2")
MsgBox Age(y, x)
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"Rick S." wrote:

I am not understanding something of Chips function.
If I run a macro with "MsgBox Age(12 / 3 / 61, 1 / 31 / 8)" I get "0 years 0
months 0 days" returned? Obviously there is at least 46 years here. What do
I have formatted wrong?

'======author Chip pearson
Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

Calculating Age In VBA (Chip Pearson)
 

Rick S. wrote:
ActiveUser.BrainCells.Add(12HundredBazillion)

I think it's brazillion. From the greeting card on which Cheney is
reporting to Bush that they have a commitment for 100 Brazilian troops
to help in Iraq, and Bush responds "That's terrific!" And you open the
card to Bush asking, "By the way, how many is 100 brazillion?"

Alan Beban