Jump to content

Find the latest date


Recommended Posts

  • Newbies

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.

Thanks for any help you may offer.

Link to post
Share on other sites
  • Newbies

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?

Link to post
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.

 

Link to post
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.

Link to post
Share on other sites
  • Newbies

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

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.