Tags: agecalculation, datediff, excel, microsoft, msdn, simply, software, strange, string, suppose, vba

datediff in excel 2003.. Agecalculation

On Microsoft » Microsoft Excel

5,283 words with 5 Comments; publish: Fri, 23 May 2008 05:38:00 GMT; (30658.59, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • 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::::]-" <shamranatalivecrew.excel.itags.org.gettonet.dk> wrote in message

      news:twwYd.14$fQ1.9.excel.itags.org.news.get2net.dk...

      > 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

      >

      #1; Fri, 23 May 2008 05:39:00 GMT
    • > 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

      #2; Fri, 23 May 2008 05:40:00 GMT
    • > 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 !

      #3; Fri, 23 May 2008 05:41:00 GMT
    • 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 !

      >

      #4; Fri, 23 May 2008 05:42:00 GMT
    • "JulieD" <JulieD.excel.itags.org.hctsReMoVeThIs.net.au> wrote in message

      news:OlYZTZtJFHA.732.excel.itags.org.TK2MSFTNGP12.phx.gbl...

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

      #5; Fri, 23 May 2008 05:43:00 GMT