cancel
Showing results for 
Search instead for 
Did you mean: 
noaal
Nintex Newbie

Calculate max date

Jump to solution

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

Labels: (1)
0 Kudos
Reply
9 Replies
orunnals
Nintex Newbie

Re: Calculate max date

Jump to solution

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!

Reply
lakshminarayana
Nintex Newbie

Re: Calculate max date

Jump to solution

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

Reply
noaal
Nintex Newbie

Re: Calculate max date

Jump to solution

Hello Lakshmi C,

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

0 Kudos
Reply
noaal
Nintex Newbie

Re: Calculate max date

Jump to solution

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

0 Kudos
Reply
lakshminarayana
Nintex Newbie

Re: Calculate max date

Jump to solution

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

0 Kudos
Reply
noaal
Nintex Newbie

Re: Calculate max date

Jump to solution

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

0 Kudos
Reply
Highlighted
orunnals
Nintex Newbie

Re: Calculate max date

Jump to solution

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.

Reply
noaal
Nintex Newbie

Re: Calculate max date

Jump to solution

Hello Owen,

Works Perfect. Thanks for your concern.

0 Kudos
Reply
orunnals
Nintex Newbie

Re: Calculate max date

Jump to solution

My pleasure! Glad it's working for you!

Reply