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

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
----

Cheers

JulieD



"-[::::Shamran::::]-" wrote in message
...
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

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

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 !

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


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" wrote in message
...
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 !