6 Replies Latest reply on Oct 11, 2016 1:36 AM by Richard van Dijk

    Calculate age from date of birth

      I'm trying to calculate the age of a person returned in a search from their date of birth by creating a calcuated field and have run into some trouble.


      If i create a calculated field titled "Age" with the forumula


      DATEDIFF('day', [BirthDate], TODAY()) / 365


      I get close, but obviously due to leap years this goes slightly wrong.


      What I'm ideally trying to do is replicate the C# code I've written below


      DateTime now = DateTime.Today;

      int age = now.Year - DateOfBirth.Year;

      if (DateOfBirth.Value.Date > now.AddYears(-age)) age--;


      Return age;


      I'm having trouble doing this in the calculated field dialog. If I do the below:


      Create two parameters, year and day


      write the code

      Year = DATEDIFF('year', [BirthDate], TODAY())

      Day = DATEDIFF('day', [BirthDate], TODAY())


      I get a syntax error on my second line. I've searched but can't appear to find any requirement for an endline character. Clearly I'm missing something here but I'm puzzled as to what. Any help would be apreciated.

        • 1. Re: Calculate age from DOB
          Alex Kerin

          What is your full calculation? You need an end after an if statement.

          • 2. Re: Calculate age from DOB

            Hi Alex,


            Thanks for your response. I believe you're referring to the C# code I posted? (I'm aware this won't work in a tableau calculated field but I'm simply using it to illustrate what I'm trying to achieve)


            If you are referring to the C# code then what I have typed above is syntactically valid, it might be a little clearer expressed as follows however:

            if (DateOfBirth.Value.Date > now.AddYears(-age))





            I'm trying to perform the same calculation in Tableau as I have in the C# code. (Sorry if that isn't very helpful but C# is my preferred language and I found it the most concise way to express my intended calculation)



            My intended full calculation will take a date of birth and return the persons age in years

            • 3. Re: Calculate age from DOB
              Marc Engle

              HI Tom,

                   Will something like the below work?  It appears to work on my side in Tableau, but I didn't test specifically for a leap year case.  This would be for the Age calculation to reproduce, at least logically, what you have in c# code above.


              IF [DOB]>DATEADD('year',-DATEDIFF('year',[DOB],TODAY()),TODAY()) THEN








              3 of 3 people found this helpful
              • 4. Re: Calculate age from DOB

                Hi Marc,


                Thanks very much. This has worked perfectly! You've saved me a lot of head scratching!


                Kind regards


                • 5. Re: Calculate age from date of birth
                  Nicholas Kamenos

                  What would need to be done to calculate age based on a fixed day? Suppose one is doing historical reporting, using TODAY() would not be accurate.


                  Can a mm/dd/yyyy be substituted for TODAY ()?

                  • 6. Re: Calculate age from date of birth
                    Richard van Dijk

                    To get Age based on a given Reporting Date use the following:


                    IF [DoB] > DATEADD('year',-DATEDIFF('year',[DoB],[Reporting Date]),[Reporting Date]) THEN


                        DATEDIFF('year',[DoB],[Reporting Date])-1




                        DATEDIFF('year',[DoB],[Reporting Date])




                    In your case [Reporting Date] could be a calculated field with DateParse, e.g. DATEPARSE("mm/dd/yyyy","10/04/2016"), or parameter driven using MAKEDATE([Year],[Month],[Day]).

                    1 of 1 people found this helpful