# Find the latest date

## Recommended Posts

I have records with 6 possible "run dates" and I need to come up with a calculation that finds the latest date of the 6. It is possible and likely that most of the fields will be empty.

12/12/19

1/1/20

2/15/20

The calculation should look at the 6 fields and determine which has the latest date.

##### Share on other sites

What you're looking for is the Max function.

Welcome to the forums.

##### Share on other sites

Wow - presto it worked!

Thanks so much, you saved me a bunch of time!

##### Share on other sites

Since that worked so well, let me ask a follow up question.

Of those 6 possible date fields how do I find which date is closest to a Today (date field).

Of the 3 dates in the original post. The result would be 12/12/19, today is 12/9/19.

Any ideas or do I need to clarify?

##### Share on other sites

First, I would suggest you use related records instead of fields.  Everything will become easier once you do that. For example, you could define a calculation field in the related table that returns the difference between the date and today, then sort the records by this field and show only the first one.

To calculate the date closest to today from a list of dates would require a rather complicated custom function - perhaps something similar to https://www.briandunning.com/cf/1266. Might be easier now that SortValues() and While() functions are available - but still not trivial.

##### Share on other sites

As @comment says, this one's more complex, and it's not clear to me whether those dates are fields in a single record, or are a single field in multiple records.

Essentially you need to do understand a couple of things:

- you can subtract one date from another to get a number of days, e.g. Get(CurrentDate) - RunDate

- you can then work with those numbers in various ways, e.g. the Min function

- if you use a calculated field to derive the date difference from the current date, you'll generally want it to be unstored so that it updates when used. Be aware that unstored fields can be slow if whatever you're doing involves large numbers of records, or e.g., you're using calculated fields for those Max/Min results rather than storing the results.

##### Share on other sites

Thanks to Comment and Fitch! I'm glad to hear you both calling this complicated, I don't feel so dumb now😄.

The good news is, from the information you have shared, I think I can come up with a solution that will work.

Ralph

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...