Finding minimum date from a list of dates

HCL Domino Domino Forum
1

I saw this question posted in the R4&5 Forum, but without a response. I have several date fields and would like to display the earliest date in a view. Seems like there should be an easy formula solution…

2

Subject: Finding minimum date from a list of dates

Hi,

there is a new @Formula in Notes 6.5 that can sort : @Sort

And it is possible to use it with a list of dates. So you can sort them and extract the first occurence of the new sorted list.

hope it helps

Dave

3

Subject: Finding minimum date from a list of dates

as simple as this…

@Min(dateField1: dateField2: dateField3)

4

Subject: RE: Finding minimum date from a list of dates

Note that this is a new behaviour for @Min (and @Max, for that matter), so you would not have seen it suggested in the R4 & R5 forum. Always ask your ND6 questions in this forum; it’s the only way you’ll get the right answer.

5

Subject: RE: Finding minimum date from a list of dates

This was first thing I tried, but the help file says that this only works with one list or two arguments. I have four fields to compare. Have you successfully used it with more than two arguments?

6

Subject: RE: Finding minimum date from a list of dates

by using : it is making the multiple values appear as a list.

7

Subject: Solution to finding minimum date from a list of dates

I tried all of these ideas with no success. Here is what worked for me…

A := @If(fld1 != “”; fld1; @Today);

B := @If(fld2 != “”; fld2; @Today);

C := @If(fld3 != “”; fld3; @Today);

D := @If(fld4 != “”; fld4; @Today);

List := A : B : C : D;

@Min(List)

8

Subject: Sorry… but for decent performance do not use @Today in a view column formula

The formula I posted before will work.

9

Subject: RE: Sorry… but for decent performance do not use @Today in a view column formula

You are right. I forgot that @today keeps recalculating itself. Thanks for your help!

10

Subject: RE: Finding minimum date from a list of dates

The other response you got assumed a multivalue field, but in your text you say you have several fields.

If all the fields contain a date, you can do something like:

@Min(field1 : field2 : field3 : …)

If some fields may be blank, it’s harder. You probably would have to do something like:

_tmp := @If(field2 = “”; field1; field1 = “”; field2; @min(field1; field2));

_tmp := @if(field3 = “”; _tmp; _tmp = “”; field3; @min(field3; _tmp));

…

leaving the _tmp := off the last line.

It may be best to do this calculation in a hidden computed field to help the views index faster.