Populating Age(from EC) on the compensation form

[former_member25713]

Hello,

We are trying to populate the age(on the Employee profile of data type "double") on the Compensation Form.

The Age is of type long and it is calculated using formula.It is showing up on the employee profile however, upon launching the form, it is showing up as '0'.We have tried the different column types on worksheet.

Regards,

Safeena

[pmacgovern · Accepted solution]

Hi

This is a common request but is not possible, because, as you say, the age is calculated by a formula when the Profile is display. It is not stored at all. The best workaround is to load the Date of Birth into a hidden column and use the dateDiff function and divide by 365.25 to get the age in years as of a fixed date (e.g. "Age as of Jan 1")

Phil

[former_member25713 · Accepted solution]

Hello Phil,

Thanks You for replying.Could you please let me know how do I default a date in a custom column?

[xavierlegarrec · Accepted solution]

Hi Safeena, here is the way to do it : https://youtu.be/pwWkOHcCCGI

The formula for the Age custom field in my recording is : round('down',(dateDiff(toDate(customDateOfBirth,"MM/dd/yyyy"),toDate("09/29/2020","MM/dd/yyyy"))/365))

[former_member677253 · Accepted solution]

Hello,

Is it possible to calculate it with a function like 'Today'? How do you choose the static date to calculate the age?

Thanks!

[xavierlegarrec · Accepted solution]

Hi, in the design proposed above you must agree on a date you use as current, in the example above I used yesterday's date (09/29/2020) and simply entered it inside quotation marks (in bold below) :

round('down',(dateDiff(toDate(customDateOfBirth,"MM/dd/yyyy"),toDate("09/29/2020","MM/dd/yyyy"))/365))

What you are asking about cannot be done through a standard formula that would recalculate the new age every day. However functionally speaking here I would ask my customer : is this a showstopper knowing that Compensation cycles are typically only 2 to 3 weeks long ?

If the customer says yes, then the only way to build what you are describing is through a lookup table that Comp Admin would have to update every day during the cycle with the current date and then run an update all worksheets.

For example :

round('down',(dateDiff(toDate(customDateOfBirth,"MM/dd/yyyy"),toDate(lookup("Todays_date",1),"MM/dd/yyyy"))/365))

Hope this helps !