Answered

Calculate an age based on DOB but also on the start date of an event

Like (0) Share

Report

Posted on 23 Apr 2023 19:21:36 by StuC

Hi have two sharepoint lists. Students and Group. Students age is calculated on Date of birth and also on the start date of the event.

If(
 IsBlank(StudentDOB.SelectedDate),
 ThisItem.StudentAge,
 Round(
 DateDiff(
 StudentDOB.SelectedDate,
 LookUp(
 Group,
 Title = GroupTitleComboBox1.Selected.Title,
 StartDate
 )
 ) / 365,
 0
 )
)

It works unless the date is passed the startdate and then it adds 1 year. any solutions out there?

Categories:

Using Apps Using Formulas

General Questions

All responses (6)

Answers (1)

Verified answer

WarrenBelz 146,702 Most Valuable Professional on 23 Apr 2023 at 22:22:37

Like (1)

Report
Re: Calculate an age based on DOB but also on the start date of an event

@StuC

You are correct (I tested it), you need

If( IsBlank(StudentDOB.SelectedDate), ThisItem.StudentAge, RoundDown( DateDiff( StudentDOB.SelectedDate, LookUp( Group, Title = GroupTitleComboBox1.Selected.Title ).StartDate, TimeUnit.Days )/365.25, 0 ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps
WarrenBelz 146,702 Most Valuable Professional on 23 Apr 2023 at 20:44:02

Like (0)

Report

Re: Calculate an age based on DOB but also on the start date of an event

@StuC ,

Then the code I posted should work - it rounds down the date period to whole years - I assume you want the actual age so 12 years and 1 day equals 12 years ?
ManishSolanki 15,085 Super User 2025 Season 1 on 23 Apr 2023 at 20:39:28

Like (0)

Report

Re: Calculate an age based on DOB but also on the start date of an event

Hi @WarrenBelz

Thanks for your suggestion but it will not give the correct result if date & month of Start event is less that the DOB. For e.g., if DOB is 1-May-2020 and Event Date is 1-Apr-2023 then it gives 23 years but the actual result should be 22 years. The result should be 23 if Event Date goes past or equal to 1-May-2023.
I have taken care of this rule in the formula which I have shared in my reply.

Thanks
StuC 50 on 23 Apr 2023 at 20:30:06

Like (0)

Report

Re: Calculate an age based on DOB but also on the start date of an event

@WarrenBelz
Based on StartDate I want to make the student a year older or not.
WarrenBelz 146,702 Most Valuable Professional on 23 Apr 2023 at 20:15:23

Like (0)

Report
Re: Calculate an age based on DOB but also on the start date of an event

Hi @StuC ,

I am not totally clear on your exact rounding requirements, but try this

If( IsBlank(StudentDOB.SelectedDate), ThisItem.StudentAge, RoundDown( DateDiff( StudentDOB.SelectedDate, LookUp( Group, Title = GroupTitleComboBox1.Selected.Title ).StartDate, TimeUnit.Years ), 0 ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps
ManishSolanki 15,085 Super User 2025 Season 1 on 23 Apr 2023 at 20:11:27

Like (1)

Report

Re: Calculate an age based on DOB but also on the start date of an event

Hi @StuC

To resolve extra year in DOB, use the below formula, assuming you need age in years only:

If(
IsBlank(StudentDOB.SelectedDate),
ThisItem.StudentAge,
With({varDOB: LookUp(
Group,
Title = GroupTitleComboBox1.Selected.Title,
StartDate
)},If((Month(StudentDOB.SelectedDate) > Month(varDOB)) Or (Month(StudentDOB.SelectedDate) = Month(varDOB) And Day(StudentDOB.SelectedDate)>=Day(varDOB)) , Year(StudentDOB.SelectedDate) - Year(varDOB),
Year(StudentDOB.SelectedDate) - Year(varDOB) -1)
))

here, idea is to subtract years of both dates if date and month part of start event is more than that of date of birth else it will be one less than the difference between the years of start event and DOB.

Please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks