I have a date field, with 21 that match the aggragate QUERYFORMULA in which I was seeking the starting and ending dates. I determined that in this case they are 7/1/21 and 1/11/22. One Aggregate formula is for “min”, the other is for “max” and they correctly return 2459397 and 2459591.
aggregate({FilledDate}, “min”, {MemberID=««MemberID»»},”Rx History”,true())+" to "+aggregate({FilledDate}, “max”, {MemberID=««MemberID»»},"Rx History”,true())
But I want them displayed as readable dates so I adjusted the formula to include a date pattern.
aggregate({datepattern(FilledDate,”mm/dd/yy”)}, “min”, {MemberID=««MemberID»»},”Rx History”,true())+" to "+aggregate({datepattern(FilledDate,”mm/dd/yy”)}, “max”, {MemberID=««MemberID»»},"Rx History”,true())
That caused them to display as 1/11/22 and 9/24/21. Huh?
Eventually I found that I had to embed the aggregate function inside the datepattern to get the desired output.
datepattern(aggregate({FilledDate}, “min”, {MemberID=««MemberID»»},“Rx History”,true()),“mm/dd/yy”)+" to "+datepattern(aggregate({FilledDate}, “max”, {MemberID=««MemberID»»},“Rx History”,true()),“mm/dd/yy”)
I can now see and understand why this format is necessary, but it wasn’t obvious. The minimum, for instance, needs to be calculated on the date, not the DatePattern. Since believable dates were actually being displayed though, I had overlooked the fact that they were off from what they should have been. I have a handful of similar errors to track down and correct.
Hopefully this will be a useful note to others, and my future self, about the need to do the formatting after getting values via aggregate.