datediff in excel 2003.. Agecalculation

[-[::::Shamran::::]-]

Strange... I simply can't get datediff to work in a string in Excel. Is it
only suppose to work in VBA ?
What I am trying to do is :

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

I have to calculate the age in F17:F500 from cell E17:E500

Can anyone help ?
Shamran

 

[JulieD]

Hi

from Chip Pearson's page on DATEDIF
http://www.cpearson.com/excel/datedif.htm
You can't use DATEDIF in VBA code. VBA provides a function called DateDiff
(note, two f's), but DateDiff doesn't supoort the "ym", "md", and "yd"
interval arguments that DATEDIF does. To compute age in VBA, you have to
do the math on your own.
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), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

 

[-[::::Shamran::::]-]

from Chip Pearson's page on DATEDIF
http://www.cpearson.com/excel/datedif.htm
You can't use DATEDIF in VBA code. VBA provides a function called
DateDiff (note, two f's), but DateDiff doesn't supoort the "ym", "md", and
"yd" interval arguments that DATEDIF does. To compute age in VBA, you
have to do the math on your own.
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), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
----

Yes that it try ! BUT I simply can't get Dateif to work in a Ceææ either ! I
really don't won't to make any VBA code. I would rather have a simple cell
calculation

Shamran

 

[-[::::Shamran::::]-]

from Chip Pearson's page on DATEDIF
http://www.cpearson.com/excel/datedif.htm
You can't use DATEDIF in VBA code. VBA provides a function called
DateDiff (note, two f's), but DateDiff doesn't supoort the "ym", "md", and
"yd" interval arguments that DATEDIF does. To compute age in VBA, you
have to do the math on your own.
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), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
----

Cheers

JulieD

Sorry Julie !! The problem was that I am suppose to use DATO.FORSKEL. I HATE
this danish crap ! When I upgrade next time it will deff. be US version !

Regards Jesper !

 

[JulieD]

Hi Jesper

so it's working now? ... great
i have enough trouble using the english version of excel i couldn't imagine
having to translate / deal with different function names - eeek!

Cheers
JulieD

 

[-[::::Shamran::::]-]

Hi Jesper

so it's working now? ... great
i have enough trouble using the english version of excel i couldn't
imagine having to translate / deal with different function names - eeek!

Belive me it's hell !