Solved

Calculate max date

  • 20 October 2017
  • 10 replies
  • 105 views

Badge +3

Good Afternoon All !

I have a Nintex 2013 Form which contains ten different date fields. My requirement is to calculate the max date in among all these. I need this to be done in the form rather using a workflow. Can someone shed some light on this.

Thanks,

Happy weekend

icon

Best answer by orunnals 21 October 2017, 01:02

View original

10 replies

Badge +4

This was a fun one! You'll need to add a calculated value to your form and then use a formula like this:

formatDate(dateAddMinutes(date(1,1,1970,0,0),max([Date 1,Date 2,Date 3])/1000/60), "MM/dd/yyyy")

Replace the Date 1, 2 and 3 and just add extra commas for your other 7 fields. 

- The max function gets you the right date but in the wrong format: Unix epoch, which is the number of milliseconds since January 1, 1970.

- You then divide that by 1000 to get seconds then by 60 to get minutes, then add those minutes to January 1, 1970 using the dateAddMinutes function. You may want to play with the two 0's in this function based on your timezone if time is important. 

- Finally you can use formatDate to get the right string version of the date. If you need the value in an actual date control that gets harder. Didn't take my POC that far.

Good luck!

Badge +9

Also you can try with 'If-else' ladder like below.

If(date1<date2,If(date2<date3,If(date3<date4,If(date4<date5,If(date5<date6,If(date6<date7,If(date7<date8,If(date8<date9,If(date9<date10,date10,date9),date8),date7),date6),date5),date4),date3),date2),date1

Badge +3

Hello Lakshmi C,

Thanks for your reply I have tried it and works perfect!

Badge +3

Hello Lakshmi,

I need the resultant date in this format MM/DD/YYYY. I used the below format but couldn't get the desired result. Is my function formula correct?

formatDate(If(date1<date2,If(date2<date3,If(date3<date4,If(date4<date5,If(date5<date6,If(date6<date7,If(date7<date8,If(date8<date9,If(date9<date10,date10,date9),date8),date7),date6),date5),date4),date3),date2),date1),"d")  

Badge +9

Use formatDate(formula,'MM/dd/yyyy')

Badge +3

Its not picking up the max date when I use formatDate(If(date1<date2,If(date2<date3,If(date3<date4,If(date4<date5,If(date5<date6,If(date6<date7,If(date7<date8,If(date8<date9,If(date9<date10,date10,date9),date8),date7),date6),date5),date4),date3),date2),date1),'MM/dd/yyyy')

Badge +4

Hi Noa al,

You should try the formula I posted. It's a bit simpler and does produce the result in the format you want:

formatDate(dateAddMinutes(date(1,1,1970,0,0),max([Date 1,Date 2,Date 3,Date 4, Date 5, Date 6, Date 7, Date 8, Date 9, Date 10])/1000/60), "MM/dd/yyyy")

The If-Else ladder has a logic issue with it. If date 1 is greater than date 2, your result is date 1 and it doesn't check the rest of the fields. It can be fixed, but it becomes quite complicated as you need to repeat the if-else structure in all the else paths as well.

Using the max function will ensure evaluation of all fields and it's much easier to read.

Badge +3

Hello Owen,

Works Perfect. Thanks for your concern.

Badge +4

My pleasure! Glad it's working for you!

Hi, I used for formula but have a #value error, can you please help?

Reply