Age Calculation based on DOB

Post Reply
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Age Calculation based on DOB

Post by ExApplix »

Hi,

I have got DOB (Date of Birth) in the cube as String format 'YYYY-MM-DD' e.g. 1961-12-23

In TM1 Rile, I want to calculate the Age of person based on Today's date.

Can someone please give me the calculation how to get it?

Thanks in advance :)
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Age Calculation based on DOB

Post by David Usherwood »

Looking through the date and time rules functions in the Reference Guide, I see
DAYNO

This is a TM1® rules function, valid in both TM1 rules and TurboIntegrator processes.

DAYNO returns the serial date number corresponding to a given date string.

and

NOW

This is a TM1® rules function, valid in both TM1 rules and TurboIntegrator processes.

NOW returns the current date/time value in serial number format.

The 'Serial Number Format' is the number of days since (from memory) 1/1/60.
Seems to cover what you need.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Age Calculation based on DOB

Post by lotsaram »

Reading between the lines, a liberal interpretation of what David has said is that you have been around TM1 long enough that given the tools at your disposal (a quick glance through the date and time rules functions section of the reference guide in this case) you should be able to go and figure this out for yourself without requesting help and wasting people's time.

I'm inclined to be more direct: given the length of time you have been posting on this board and the length of time you have been exposed to TM1 I would have thought you could figure this out for yourself.

I hope you don't find that too harsh. To take the sting out here's a simple example of how you might do the calculation to return a decimal year.

Code: Select all

['Age in Years'] = N: 
( NOW() - DAYNO(DB('EmployeeData', !Employee, 'DOB')) ) / 365;
I hope you agree that was not too difficult.
(My apologies to you David if I was putting any words in your mouth that weren't actually stuck in your throat!)
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: Age Calculation based on DOB

Post by ExApplix »

Ya I tried to convert the Date using DAYNO and subtract the serial number of the Today() but the answer which I get does not seem to be right.

Can someone please give me syntax here?

Thanks
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Age Calculation based on DOB

Post by lotsaram »

ExApplix wrote:Ya I tried to convert the Date using DAYNO and subtract the serial number of the Today() but the answer which I get does not seem to be right.

Can someone please give me syntax here?

Thanks
I have given you the correct syntax for the problem which you have described. TODAY() does not return a serial date, it returns a formatted date string, hence use of the NOW() function. If you need to calculate a date based not on the current time but on a date calculated by a a dimension element, intersection, or a string value then you would need to reuse DAYNO. If you need further assistance I suggest you re-read the posts above.
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: Age Calculation based on DOB

Post by ExApplix »

lotsaram wrote: I'm inclined to be more direct: given the length of time you have been posting on this board and the length of time you have been exposed to TM1 I would have thought you could figure this out for yourself.
Lotsaram, Thanks for the reply but I just want to say one thing that sometimes we get stuck in really simple things or it could be the first time we are dealing with something. Also, we all come here on this forum to help each other. So there is no point for getting rude or harsh to anyone. So please dont get anything from this forum on your nerves.

Getting back to the actual question:
I came to knew that TM1 dates cant handle dates prior t0 1960, so if the DOB is prior to 1960 then how can we handle this? Is there a special function to do this?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Age Calculation based on DOB

Post by lotsaram »

ExApplix wrote:Getting back to the actual question:
I came to knew that TM1 dates cant handle dates prior t0 1960, so if the DOB is prior to 1960 then how can we handle this? Is there a special function to do this?
I see. Then DAYNO won't work for you. Date indexing starts in TM1 from 01 Jan 1960. Where the date indexing starts from is a completely arbitrary decision, it has to start from somewhere but why 01.01.1960 who knows? Alan Kirk has posted about this at great length so I won't go into it further.

The simplest way to tackle this problem is to subtract the time serial numbers of two dates and divide by 365 to convert days into years. However if you have a date prior to the commencement of the date index then this won't work and you will need to resort to a moderately more complex method involving sub-stringing your formatted dates and converting the years into integers and the months to fractions to add to the years then do the subtraction. The length that you need to go to depends on the accuracy that you require, ... you could also convert the days to a month fraction and if you wanted to go the whole way and be absolutely accurate then you would need lookup cubes to grab the correct number of days depending on the month etc.

But here's a simplified example:

Code: Select all

['Age in Years'] = N:
( TIMVL(NOW(), 'Y') + ( TIMVL(NOW(), 'M') / 12 ) )
 - 
(
  NUMBR(SUBST(DB('EmployeeData', !Employee, 'DOB'), 1, 4))
  +
  ( NUMBR(SUBST(DB('EmployeeData', !Employee, 'DOB'), 6, 2)) / 12 ) 
);
Had you explained your problem in more detail and the lengths you had already taken to solve it and given an example of your code (read the request for assistance guidelines) then my original response would have been different. A number of people including myself are happy to assist others but I think I speak for all when I say that I am much happier to offer assistance when there is a demonstrated effort on behalf of the person asking for help to have actually done some thinking about the problem and taken some steps themselves to solve it rather than reaching out for help immediately as a first resort. Free-loading lessens the willingness of others to offer assistance. Especially for people who have been around for a while and are asking for assistance I think it is imperative to show that some sort of effort has been made. It is also nice to once in a while contribute back to the community by answering questions as well.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Age Calculation based on DOB

Post by tomok »

lotsaram wrote:A number of people including myself are happy to assist others but I think I speak for all when I say that I am much happier to offer assistance when there is a demonstrated effort on behalf of the person asking for help to have actually done some thinking about the problem and taken some steps themselves to solve it rather than reaching out for help immediately as a first resort. Free-loading lessens the willingness of others to offer assistance. Especially for people who have been around for a while and are asking for assistance I think it is imperative to show that some sort of effort has been made. It is also nice to once in a while contribute back to the community by answering questions as well.
Preach on, brother!
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Age Calculation based on DOB

Post by MarenC »

Hi,

maybe its me but I just couldn't get DayNO or TimVl to work, too many out by 1.

So I did the following (my date is in dd/mm/yyyy format):

Code: Select all

['EmpAge']=S:		
	If(		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) = Month(Today(1)) & 	
		Day(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Day(Today(1))	
		,	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
	If (		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Month(Today(1)),	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
			Str( NUMBR(Subst(Today(1),1,4)) - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0)
	));
I posted this because I don't know why Dayno or Timvl were suggested and wondered if I was missing something!

I was also confused as to why I needed to convert this numeric attribute to string but I understand all attributes are actually string, so any rules in the attributes cube must be S?

Maren
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Age Calculation based on DOB

Post by tomok »

MarenC wrote: Tue Jun 09, 2020 3:34 pm Hi,

maybe its me but I just couldn't get DayNO or TimVl to work, too many out by 1.

So I did the following (my date is in dd/mm/yyyy format):

Code: Select all

['EmpAge']=S:		
	If(		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) = Month(Today(1)) & 	
		Day(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Day(Today(1))	
		,	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
	If (		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Month(Today(1)),	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
			Str( NUMBR(Subst(Today(1),1,4)) - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0)
	));
I posted this because I don't know why Dayno or Timvl were suggested and wondered if I was missing something!

I was also confused as to why I needed to convert this numeric attribute to string but I understand all attributes are actually string, so any rules in the attributes cube must be S?

Maren
A ridiculuous amount of work when all you have to do is store Age as a numeric serial number. You can display a serial number in "dd/mm/yy", or "mm/dd/yyyy", or whatever, format simply by applying the proper date format to the attribute cube.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Age Calculation based on DOB

Post by MarenC »

Hi,

Thanks for taking the time to respond tomok.

I did ask if I had missed something! Which I still must be because I can't work out how your method gets me someones age, I can only apologise for that!

So are you saying to store their Date of Birth as a serial date in the attribute cube and format this element (Date of Birth) as required.

But how does that get me any nearer to then saying if this employee is 30, 29 or 52?

Maren
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Age Calculation based on DOB

Post by tomok »

MarenC wrote: Tue Jun 09, 2020 4:31 pm Hi,

Thanks for taking the time to respond tomok.

I did ask if I had missed something! Which I still must be because I can't work out how your method gets me someones age, I can only apologise for that!

So are you saying to store their Date of Birth as a serial date in the attribute cube and format this element (Date of Birth) as required.

But how does that get me any nearer to then saying if this employee is 30, 29 or 52?

Maren
So, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Age Calculation based on DOB

Post by Wim Gielis »

tomok wrote: Tue Jun 09, 2020 8:40 pmSo, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?
I would first of all divide by 365.25 instead of 365. Where I live, we have the concept of leap years.
After that, apply some rounding too. The answer will never be fully accurate in all cases, though.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Age Calculation based on DOB

Post by tomok »

Wim Gielis wrote: Tue Jun 09, 2020 9:42 pm
tomok wrote: Tue Jun 09, 2020 8:40 pmSo, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?
I would first of all divide by 365.25 instead of 365. Where I live, we have the concept of leap years.
After that, apply some rounding too. The answer will never be fully accurate in all cases, though.
Yes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Age Calculation based on DOB

Post by Wim Gielis »

tomok wrote: Tue Jun 09, 2020 11:52 pmYes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.
When doing a DOB calculation, why would one ever want to use fractional years ?

Put differently, I was born January 19,1981. So I turned 39 on January 10, 2020, or on January 19, 2020 ?
Who would like to have such an inaccurate answer ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Age Calculation based on DOB

Post by Alan Kirk »

Wim Gielis wrote: Tue Jun 09, 2020 11:58 pm
tomok wrote: Tue Jun 09, 2020 11:52 pmYes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.
When doing a DOB calculation, why would one ever want to use fractional years ?

Put differently, I was born January 19,1981. So I turned 39 on January 10, 2020, or on January 19, 2020 ?
I'd be quiet about that if I were you; you could be skewering your chances to get in as FC Lucyvo's next striker. There may still be hope for you as a no nonsense centre back, though. The oldest, er, I mean, most mature Italian player I know of (in Serie B if I recall correctly) was a 45 year old GK who the rest of the team called "Nonno" (grandpa). Yes, 45. That's depressing.
Wim Gielis wrote: Tue Jun 09, 2020 11:58 pm Who would like to have such an inaccurate answer ?
Agreed. It can also have other effects as well though admittedly they're edge cases. For example some government benefits, tax concessions, etc kick in at an exact number of years, which can have an impact on calculations within a payroll budgeting model. To give a random example in Australia if you are over... 50, I think it is? (I don't have the model up at the moment) you need to get an extra week's pay if you are made redundant. In cases like that calculating age is such a pain in the backside because of leap years (and the omission of leap years in some centuries) that I don't even bother doing it. I'd rather take the day, month and year components from the birth date, whack {50} (or whatever; because the law can change I have this sort of thing as a value in a system cube) onto the year, then just work out whether the resulting serial date is < the current one. This calculation is kept in a cube which contains employee master data, just as a 1 if the rule applies, 0 if not.

Realistically or at least theoretically, in Australia at least, that kind of calculation should be the ONLY reason for working out an age. Under the anti-discrimination law asking a person's age in an interview is off limits, and discriminating against someone on the basis of age is also off limits... up to a point. To use the example above you would be hard pressed to say that a person's age is immaterial to the coach / manager of a calcio team. "You're 49, and you want to be my new box to box midfielder? Yyyyeah, I gotta tell you, nonno, I foresee some complications here." However that aside, in a normal industrial or commercial firm the question would need to be asked about why a manager would need to know the ages of his employees if not for the purpose of culling. Which is, of course, illegal. Here, anyway. In theory, anyway.

Of course anyone is free to ask me my age. If I'm in a good mood I'll say "over 21". If not, it's more likely to be "**** off and mind your own gorram business, and yes, Ferrari SHOULD have picked me over Sainz".

Interestingly Excel has the same basic problem. I don't know which genius at Microsoft wrote this document, but:
Some Microsoft 'Droid Who Can't Count wrote:Excel can help you calculate the age of a person in different ways. The table below shows common methods to do so, using the Date and time functions.
=(YEAR(NOW())-YEAR(A2))
The result is the age of person—the difference between today and the birthdate in A2.
In the immortal words of my poker buddy Brock Samson, "ARE YOU FREAKIN' KIDDING ME??" For someone whose birthdate is 31 December, that will give the wrong answer for 364 out of 365 days most years, and 365 out of 366 the other ones. If the person has yet to have their birthday in the current year, it will again be wrong. The solution in Excel is of course the DateDif function, which you WON'T find in the formula wizard because according to Microsoft it's there for Lotus compatibility only, and may return the wrong value in some cases (though not, as far as I'm aware, for age calculations). So rather than, oh, I dunno, FIXING the function so that it DOESN'T ever return an error, or alternatively creating an explicit Age() function, MS finds it easier to just pretend it isn't there.

Though that does raise the question of whether an Age() TI/Rules function might be a useful target for an RFE in TM1. I'm not sure that I have enough skin in that game to bother with it, but others may.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Age Calculation based on DOB

Post by MarenC »

Hi,

The need for this is to drive payroll calculations/forecasts. So while it doesn't have to be 100% accurate I would rather not have the conversation
with users about why it isn't!

Because the DOB was already being held as a text attribute in dd/mm/yyyy format I wanted to calculate the age based on this existing data.

I did try to simplfy the formula as follows:

Code: Select all

['EmpAge']=S:
	Str(Int((Now() - 
	Dayno(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2))) \ 365.25),3,0);

However as suggested above anyone born before 1960 and it returns zero.

I am left with my original formula which takes each component of the Age, Year, Month and Day. It may be long winded
but it seems to be calculating correctly.

Now for a forecast I need to know what someone's age will be by year/month into the future so I think I will ultimately hold this in a cube
but was just wanting to nail down the principle before getting my hands too dirty.

I appreciate everyone taking time to help me out.

Maren
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Age Calculation based on DOB

Post by Alan Kirk »

MarenC wrote: Wed Jun 10, 2020 7:56 am Hi,

The need for this is to drive payroll calculations/forecasts. So while it doesn't have to be 100% accurate I would rather not have the conversation
with users about why it isn't!

Because the DOB was already being held as a text attribute in dd/mm/yyyy format I wanted to calculate the age based on this existing data.

I did try to simplfy the formula as follows:

Code: Select all

['EmpAge']=S:
	Str(Int((Now() - 
	Dayno(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2))) \ 365.25),3,0);

However as suggested above anyone born before 1960 and it returns zero.
You may want to consider switching on the UseExcelSerialDate=T parameter in your config file. That will use Excel's 1900 starting point, and I'm assuming that none of your employees are 120 years old.

(Why wasn't this raised above? Because the earlier posts in this thread are from 2011, 9 years ago. It didn't exist then.)
Enables the use of Microsoft Excel serial dates instead of TM1® serial dates.

Parameter type: optional, static

When UseExcelSerialDate is enabled, TM1 rule functions and TurboIntegrator functions use Jan 1, 1900 as a base date for serial dates instead of Jan 1, 1960.

In the past, TM1 rule functions used serial dates that represent the number of days elapsed since Jan 1, 1960. This conflicts with Microsoft Excel serial dates, which represent the number of days elapsed since Jan 1, 1900. The number formatting features in TM1 expect cube data to use Microsoft Excel serial dates rather than TM1 serial dates.

To avoid the need to convert dates, enable UseExcelSerialDate to have rule functions use Microsoft Excel dates rather than legacy TM1 dates.
Be thee warned!

You will need to test the living hell out of this change in your dev server to make sure that this doesn't break any existing code or rules before you make the change on your prod server.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Age Calculation based on DOB

Post by gtonkin »

I do something different. I basically treat months and days as decimals on the year and then do some subtraction and rounding to get the age.

Code: Select all

['Age']=N:
INT(
(NUMBR(SUBST(TODAY(1),1,4))+
NUMBR(SUBST(TODAY(1),6,2))\12+
NUMBR(SUBST(TODAY(1),9,2))\365.25)
-
(NUMBR(SUBST(DB('Employee',!Employees,'DOB'),1,4))+
NUMBR(SUBST(DB('Employee',!Employees,'DOB'),6,2))\12+
NUMBR(SUBST(DB('Employee',!Employees,'DOB'),9,2))\365.25)
);
When you test the above with someone who was born on 1920-06-11, you get 99 years old and when you set the DOB to 1920-06-10, you get 100 as expected. Seems to work consistently, tested as far back as 0020-06-11 to get 1999 and 0020-06-10 to get 2000. YMMV

p.s as the months and days are reduced to fractions, the 29th of February is taken care of and people age very close to the 1st of March
Post Reply