Skip to Content

Hello All,

I came across a thread some days back where an end user wanted the Age of a customer in X Years Y Months and Z Days format given tow dates.

I searched through a lot of blogs but most of them didn’t considered leap year which may lead to an incorrect data.

I tweaked the logic which I found in one of the blog and got that working so thought of sharing with all of you as we may come across such requirements in future.

So here how it goes. I will take two dates as Current Date([CurrDate]) and  Birth Date([BDate]).

First I will calculate the number of years in a formula which I have been told by an expert that breaking a huge formula into smaller pieces makes it more readable and easy to understand

[NumberOfYears]=Year ([CurrDate]) – Year ([BDate]) – If ( [CurrDate] <[BDate];1 ; 0 )+” Years “

Now I will calculate the number of months

[NumberOfMonths]=(Mod (((MonthNumberOfYear([CurrDate]) – MonthNumberOfYear([BDate])) + 12) – If ( DayNumberOfMonth([CurrDate]) < DayNumberOfMonth([BDate]) ; 1 ; 0 ); 12 ))+” Months “

And last the number of Days

[NumberOfDays]=(DayNumberOfYear([CurrDate]) – DayNumberOfYear( [BDate]) + If ( DayNumberOfYear([CurrDate])  >= DayNumberOfYear( [BDate]) ; 0 ; If ( DayNumberOfYear( [CurrDate]) – DayNumberOfYear([CurrDate])  < DayNumberOfYear( [BDate]) ; DayNumberOfYear( [BDate]) ; DayNumberOfYear( [CurrDate])- DayNumberOfYear( [CurrDate])  ) ))+” Days”

After we do all this only task left is to concatenate these three formula’s.

[Age]=[NumberOfYears]+[NumberOfMonths]+[NumberOfDays]

and you will get the data in below format.

Capture.JPG

I have tested the code with Leap year Dates and some other random dates but your valuable feed backs and suggestions are always welcome.

Hope you like it.

Regards

Niraj

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Mahboob Mohammed

    Great effort Niraj.

    One minor update that’d make this trick flawless is, to check if the NumberofYears = 1, then the text should say “Year” or else “Years“. Similar update for Month/Months and Day/Days as well.

    [NumberofYears] = If(Year ([CurrDate]) – Year ([BDate]) – If ( [CurrDate] <[BDate];1 ; 0 )) <> 1

    Then

    (Year ([CurrDate]) – Year ([BDate]) – If ( [CurrDate] <[BDate];1 ; 0 ) +” Years “)

    Else

    (Year ([CurrDate]) – Year ([BDate]) – If ( [CurrDate] <[BDate];1 ; 0 )+” Year “)

    Hope that makes sense.

    Thanks,

    Mahboob Mohammed

    (0) 
    1. Former Member Post author

      Good catch Mahboob!! That’s the reason I shared this one to get perfect code.

      Thanks again for your feedback like you always do.

      Regards

      Niraj

      (0) 
      1. Former Member

        One more logic we can add if date is null, eg: Birthdate isn’t captured for some users then we get vague results
        to avoid that i have used

        =If (IsNull([Birth Date])=0) Then your below code
        in my case we had DOJ so it was null for some users so had to only modify the above part
        else your logic works correctly 😎

        (0) 
  2. Former Member

    Hi Niraj,
    I got some modifications to the above logic

    i wanted to display difference between dates in year quarter month

    but instead of the above format

    they wanted like

    eg: Bdate- 04/03/1964 CurrDate: 25/05/2016

    So it should display 52 years 2months 21days

    days should be the remaining days left after years and months

    i just modified the days logic and it worked
    thought to share it with everyone 🙂

    NumberOfDays:

    =If (IsNull([DOJ])=0) Then (DayNumberOfMonth(CurrentDate()) – DayNumberOfMonth([DOJ]) + If (DayNumberOfMonth(CurrentDate()) >= DayNumberOfMonth( [DOJ]) ; 0 ; If ( DayNumberOfMonth(CurrentDate()) – DayNumberOfMonth(CurrentDate()) < DayNumberOfMonth([DOJ]) ; DayNumberOfMonth( [DOJ]) ; DayNumberOfMonth

    (CurrentDate())- DayNumberOfMonth(CurrentDate()) ) ))+” Days”

    Else “”

    (0) 
  3. Former Member

     

    Chaps, I could use some help with this – I’m nearly there but it’s not quite right.

    I need to calculate and display as an age, in Years Months and Days the age of a person on the date at which an event occurred. I have the 2 critical dates, and I thought I’d copied the various bits of code correctly here, but something’s going wrong.

    Here is the specimen calculation: Birth Date = 24/04/2006 Grant Date = 03/04/2012 so the correct answer is 5 years 11 months 11 days including the end date

    This is my Years: =Year([Grant Date]) – Year([Birth Date]) – If([Grant Date] >[Birth Date];1;0) +
    ” Years” because using < added an extra year to the output

    This is my Months: =Mod(((MonthNumberOfYear([Grant Date])-MonthNumberOfYear([Birth Date]))+12)-If(DayNumberOfMonth([Grant Date])<DayNumberOfMonth([Birth Date]);1;0);12)+” Months ”

    This is my first attempt at Days:
    =DayNumberOfYear([Grant Date])-DayNumberOfYear([Birth Date])+If(DayNumberOfYear([Grant Date])>=DayNumberOfYear([Birth Date]);0;If(DayNumberOfYear([Grant Date])-DayNumberOfYear([Grant Date])<DayNumberOfYear([Birth Date]);DayNumberOfYear([Birth Date]);DayNumberOfYear([Grant Date])-DayNumberOfYear([Grant Date])))+” Days ”

    ……which produced an answer of 5yrs 11 months 94 days

    I then modified Days:
    =DayNumberOfMonth([Grant Date])-DayNumberOfMonth([Birth Date]) + If (DayNumberOfMonth([Grant Date]) >= DayNumberOfMonth([Birth Date]) ; 0 ; If ( DayNumberOfMonth([Grant Date])-DayNumberOfMonth([Grant Date]) < DayNumberOfMonth([Birth Date]) ; DayNumberOfMonth([Birth Date]) ; DayNumberOfMonth([Grant Date])- DayNumberOfMonth([Grant Date]) ) )+”Days”

    … which now produces 3 Days or, if I change >= to <= it produces -21 Days, which I thinking is something to do with the literal difference between 24 and 03?

    Your help would be much appreciated. Please do not assume any great knowledge, and feel free to explain stuff to me like I’m a five year old

     

    (0) 

Leave a Reply