Question

calculate time between two dates in forms

Forms

Updated March 6, 2017

asked on March 3, 2017

We have a Job aplication form, we trying to calculate how long a person lasted in his last job, given start date and final date.

how can we achieve this, that the result be days, month and year the person works in that place.

0 0

Replies

replied on March 3, 2017

This is the formula I use in Excel, with the two date fields having named ranges of Start_Date and End_date:
=DATEDIF(Start_date,End_Date,"Y") & " year(s), " & DATEDIF(Start_date,End_Date,"YM") & " month(s), " & DATEDIF(Start_date,End_Date,"MD") & " day(s)"
If I use values of 1/15/2015 and 3/3/2017 it correctly returns: 2 year(s), 1 month(s), 16 day(s)

Therefore, in theory, the exact same formula should work in Forms, assuming the two date fields have variable names of Start_Date and End_date. However, when I place that formula into a Single Line Field, and enter the dates 1/15/2015 and 3/3/2017 it returns: 2year(s),2month(s),-12day(s)

Although that result is technically true, it's not very helpful with the extra month and negative days. I also don't know why it dropped the spaces between the words...

0 0

replied on March 3, 2017

And the help guide doesn't help, it doesn't have DATEDIF included in the instructions (https://www.laserfiche.com/support/webhelp/Laserfiche/10/en-US/administration/#../Subsystems/Forms/Content/FieldCalculations.htm).

I think we need someone from Laserfiche to potentially look into why the DATEDIF formula does not operate as expected.

0 0

replied on March 3, 2017

It's not just the months and days that are off, the whole datedif formula sees to get messed up anytime the day of the month from the Start_Date value is later in the month than the day of the month from the End_date value.

For example, 3/5/2015 to 3/3/2017
should be displaying like this: 1 year(s), 11 month(s), 26 day(s)
but is actually displaying as: 1year(s),0month(s),-2day(s)

So it really looks to me like there is a bug in the DATEDIF formula...

0 0

replied on March 6, 2017

As https://www.exceltip.com/excel-date-time-formulas/calculating-number-of-days-weeks-months-and-years-between-dates.html mentioned, the DateDif in Excel is buggy as well, you can use following formula to manually calculate it:

=FLOOR((End_Date-Start_Date)/365.25,1)&"year(s),"&FLOOR(MOD((End_Date-Start_Date)/365.25,1)*12,1)&"month(s),"&FLOOR(MOD((End_Date-Start_Date)/30.4375,1)*30.4375,1)&"day(s)"

@████████, the spaces in used in the calculation are trimmed, this is a known issue for Forms.

1 0

You are not allowed to follow up in this post.

Question

Question

calculate time between two dates in forms

Replies

Sign in to reply to this post.