Answered

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

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Manish Solanki 15,167 Moderator on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 154,799 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
StuC 50 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz
Based on StartDate I want to make the student a year older or not.

Was this reply helpful? Yes No
Manish Solanki 15,167 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 154,799 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@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 ?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,799 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No