+3
I am trying to recreate a Excel calculation in a Nintex Responsive form which converts a a value to H:MM.
In Excel we use =TEST(L24/24,”[h]:mm” to show the results of L24 as Hours and Minutes.
How can I recreate that in Nintex Response Form Designer?. I have tried all the convert to options without success!
+16
Hi
Could you confirm which platform that you are using? Nintex O365, Nintex SharePoint, NAC/NWC
and which form designer - New Responsive, Responsive, Classic
You wish to accomplish the following - converting a Datetime value into a string “[h]:mm”
+3
Garrett
We are using SharePoint Online (Office 365) with Nintex Forms for Office 365 - Responsive Designer.
Thanks
+16
Hi
Is it something like this?
Field1 is a Calculated Value field with the following formula
+3
NO, the source data is NOT a date time field, it is a DEMICAL or INTEGER. In Excel you can take the results and divide them by 24 (hours) and display as H:mm
=TEXT(L24 / 24, “[h]:mm”)
+16
Could you provide 2-3 examples. Input value and the desired output.
Is this correct?
Input 4.5 => Output 4:30
+3
This is the Excel calculations and formulas.
+16
Hi
Sorry, I don’t think I am able to assist you.
There doesn’t seem to be any equivalent runtime formula for the Excel Formula.
+13
Could you kindly re-attach the document again? I don’t seem to be able to download it.
Also what is the contents of L24 as this is not on the screenshot.
If the formula builder in responsive forms designer is no sufficient, it might be worth moving to the new-responsive forms designer that has a much more capable formula builder, I am certain it will work.
+13
Hi, I managed to get the file from a colleague.
I would like to learn more about the overall set of calculations, there are some parts that I am not sure about, specifically how Full-time equivalent is calculated, that aside it seems to be as simple as converting an hour value (4 hours) into the time equivalent (4:00) is this correct?
If so it is possible to do this but there are a couple ways to solve it depending on the scenario.
With the way that time works a simple converting hours to time will limit to 24 hours as any more than that will loop into the next day, for example if i convert 25 hours to time it shows as 1:00 as this is 1am the next day.
This can be done easily by first setting a ‘dummy date’ I use form variables with 1/1/2000:
With that you can then add the number of hours to it using the following formula in a calculated field.
dateAddHours(_vardate, Number of hours)
I have a text field for my number of hours, the formula will output looks like this:
Then we can format that to remove the date and seconds using this formula:
As you can see below if we exceed 24 hours this formula fails as to the formula the date has increased.
Excel appears to have a special function for that which is the square brackets around the hour, this same function does not exist in web/javascript/jquery, not OOB.
After some fun I did work it out as shown below.
see attached the form, you can easily combine all of these calculated fields into 1 big one or just hide them.
+3
Jake
Attached is the current Excel Workbook which contains the calculations I am trying to replicate in Responsive Form Designer.
In the interim I will try your solution and see how I go.
Thanks
+3
I appreciate the time and effort in your response, unfortunately this did not resolve the issue I have with emulating the Excel formula. The form was created using NEW RESPONSIVE DESIGNER!
+16
Hi
For any form related matters the first thing we do is to clarify which type of Form that the user is using. Nintex has 3 types of form - which is - New Responsive, Responsive and Classic - Form types.
You stated earlier that you were using the Responsive form. As such, the solutions which was provided was based on the Responsive form.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.