+3Good 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!
+4This 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!
+9Also 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)
+3Hello 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")
+3Its 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')
+4Hi 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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.