Min/Max Dates with Formula Queries

Question

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

markshnier__you · Answer

Try converting your text result to a date.&nbsp;

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

michaelfrishma1 · Answer

Thanks for the help, but that's not working. If I keep the field as a Date/Time, then I get "Expecting datetime but found date". But if I change the field to a Date field, I lose the flight arrival time which I need to get the Min.

------------------------------
Michael Frishman
------------------------------

markshnier__you · Answer

Ok, right. &nbsp;You need to convert to a date time fieldhttps://login.quickbase.com/db/6ewwzuuj?a=dr&amp;rid=143&amp;rl=gyqThe issue is that before you can use the ToTimeStamp function, you need &nbsp;it parse out the date from the time in each Part.&nbsp;Post back if you need help with thay syntax.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

Min/Max Dates with Formula Queries

3 Replies