Skip to main content
Nintex Community Menu Bar
Solved

Calculate max date


Forum|alt.badge.img+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

Best answer by orunnals

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!

View original
Did this topic help you find an answer to your question?

10 replies

Forum|alt.badge.img+4
  • 14 replies
  • Answer
  • October 20, 2017

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!


Forum|alt.badge.img+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


Forum|alt.badge.img+3
  • Author
  • 24 replies
  • October 23, 2017

Hello Lakshmi C,

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


Forum|alt.badge.img+3
  • Author
  • 24 replies
  • October 23, 2017

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


Forum|alt.badge.img+9

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


Forum|alt.badge.img+3
  • Author
  • 24 replies
  • October 24, 2017

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


Forum|alt.badge.img+4
  • 14 replies
  • October 24, 2017

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.


Forum|alt.badge.img+3
  • Author
  • 24 replies
  • October 24, 2017

Hello Owen,

Works Perfect. Thanks for your concern.


Forum|alt.badge.img+4
  • 14 replies
  • October 24, 2017

My pleasure! Glad it's working for you!


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

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings