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?
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
@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 ?
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
Based on StartDate I want to make the student a year older or not.
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
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
WarrenBelz
146,743
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,079
Most Valuable Professional