Skip to main content
Question

Min/Max Dates with Formula Queries

  • November 21, 2021
  • 0 replies
  • 375 views

Forum|alt.badge.img+3
I need to use a formula query to find the minimum and maximum date/time for a group of records (Flight Arrival Date/Time) in the same table. I know that there is currently no ability to find the min/max inside the formula query, but I'm trying to figure a way around that.

I can get the query formula field to display "09-24-2021 01:45pm; 09-24-2021 4:00pm; 09-24-2021 12:24pm..." and so on.

At the end of the day, I want a date/time field that will show me "09-24-2021 12:24pm" as the minimum (earliest) of the three date/times listed above.

If I use this formula, it gives me a single date/time, but it's not the earliest one.
Min(
Part(ToText([FormulaQuery]),1,";"),
Part(ToText([FormulaQuery]),2,";"),
Part(ToText([FormulaQuery]),3,";"),
Part(ToText([FormulaQuery]),4,";"))

I can get it to work if I use only the time portion, as that can be converted to a numeric decimal, but that won't work for my purposes since a flight may come in at a later time but an earlier date.

Doesn't seem I can take a textlist field, "Part" it, convert it to a date/time, and find the min/max.

Am I missing anything?

------------------------------
Michael Frishman
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24

Try converting your text result to a date. 

Min(
ToDate(Part(ToText([FormulaQuery]),1,";")),
ToDate(Part(ToText([FormulaQuery]),2,";")),
ToDate(Part(ToText([FormulaQuery]),3,";")),
ToDate(Part(ToText([FormulaQuery]),4,";")))



------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------