Display a date calculation?

[SageCat]

Hi again.

Is it possible to have a date calculation field? For example, an age field which is automatically calculated from a Birth Date field and today? I assume it would likely be a column in the table which the front end would then display but I’m not sure how to go about doing this.

Thanks again

[Darren_Murphy]

Use a Math column with the following formula:

Trunc((Now-DOB)/365.25)

In the above, you have two replacements:

• Now is a special value representing the current date/time
• DOB is the column that contains the birthdate.

Here is how it looks:

The calculation isn’t precise, but should be good enough for this purpose.

[SageCat]

Thanks for this. I figured it was tucked somewhere in there but I wasn’t sure. I actually need it to show years months and days. Is that possible? It might be 0 years 4 months and 3 days.

[SageCat]

And thank you again, Darren, for all this help you are giving! You’re amazing with your knowledge on this and your speed in helping! Thank you so much!

[Darren_Murphy]

@SageCat

I actually need it to show years months and days. Is that possible? It might be 0 years 4 months and 3 days.

Okay, that’s a little more complicated, and there are a few ways to do it. Most involve several columns to extract the separate parts and then join them back together with a template column.

In this case I would probably use a JavaScript column and write a code snippet to do the heavy lifting. Don’t have time to give you an example now, but if nobody else offers up a solution I can take a look a little bit later.

[Darren_Murphy]

@SageCat - here we go…

let birthDate = new Date(p1);
let today = new Date();

let years = today.getFullYear() - birthDate.getFullYear();
let months = today.getMonth() - birthDate.getMonth();
let days = today.getDate() - birthDate.getDate();

if (days < 0) {
    months--;
    let lastMonth = new Date(today.getFullYear(), today.getMonth(), 0);
    days += lastMonth.getDate();
}

if (months < 0) {
    years--;
    months += 12;
}

return `${years} years, ${months} months, ${days} days`

[SageCat]

Oh wow. Thank you. I will have a go getting this to work.

[SageCat]

That works fantastically! Thank you so much!