Date comparison calculation when one date is in a table?

asked on September 24, 2016

Hi guys,

I know how to calculate the difference between two dates when they're in the same row of a table, but can't seem to get the formula/function right when trying to use the Forms 10.1 calculations to subtract today's date from a date in a table field.

Travel Request example: Submission date = today

There's a one-row table "Departure Details" containing: destination, date, time, flight type

I need to check that the departure date is not less than 2 weeks from today.

I tried various syntaxes within the field's calculation formula along the lines of "=DepartureDate-14" and "=DepartureDate-today" and so on, trying with the INDEX and ROW references to the date cell in the table, using SUM etc, but couldn't get it to work. I'm at the stage of creating an additional "difference" field on the form that I'm populating via javascript.

Is there a better/easier way to do this?

Thanks,

Mike

0 0

replied on September 25, 2016

In order to get the date variables available to any other fields try the below formula on the field you want calculation on (i.e in your case departure details).

=DATE(YEAR(DATE1),MONTH(DATE1),DAY(DATE1)+14)

Here DATE1 is the variable name of another Date field I had (i.e in your case Submission date).

So adding 14, it will add 14 days to submission date and assigns the corresponding value to Departure date.

For example :

If Submission date=26/9/2016, then Departure date = 10/10/2016 if above function is added to departure date.

Hope this helps.

Best Regards

0 0

View 6 previous replies

replied on September 25, 2016

Hi Akhil,

Thanks for that, but it's not quite what I'm after. The submission date is today's date, the requestor enters the departure date, I need the system to check that the date that they have entered for the departure is not within 14 days from today's date. So I'm not trying to populate the date fields, I'm needing to calculate the difference between them. I can achieve it with javascript, however I was hoping there was a way to achieve it in the formula field.

Thanks,

Mike

0 0

replied on September 25, 2016

Hi Mike ,

To calculate the difference between two dates there is a function DATEDIF available in laserfiche forms.

Its syntax is as below

=DATEDIF(date1, date2, string)

The above function returns the difference between date1 and date2. String specifies return unit: "Y"/"y" for years, "M"/"m" for months, "D"/"d" for days.

Regards.

0 0

replied on September 25, 2016

Thanks for that Akhil, I was getting hopeful there but no luck I'm afraid. I suspect it's to do with trying to reference one of the dates from a table field.

0 0

replied on September 25, 2016

For columns inside table, please use calculation such as following:

=DATEDIF(INDEX(Table.date1,ROW()),INDEX(Table.date2,ROW()),"d")

0 0

replied on September 26, 2016

Hi Xiuhong, that works fine if both date fields are in a table row, but the problem I've run into is that one date is in a table, the other isn't, so the formula fails e.g.:

=DATEDIF(Date1,INDEX(Table.date2,ROW()),"d")

I've tried with ...ROW(1)).. but that didn't work either.

0 0

SELECTED ANSWER

replied on September 28, 2016

In this case, you should use =DATEDIF(Date1,INDEX(Table.date2,1),"d"): 1 is the row number. ROW() is for getting the row number.

1 0

replied on September 28, 2016

That's what I was after, thanks Xiuhong!

0 0

replied on October 6, 2020

I have a similar situation and am not having luck. I am using the following:

=DATEDIF(Post_Closing_Dates.File_Receipt_Date,INDEX(Submitted_For_Recording,ROW()),"d")

Any suggestions?

0 0

replied on October 6, 2020

I actually got it working using the following:

=DATEDIF(INDEX(Post_Closing_Dates.File_Receipt_Date,1),Submitted_For_Recording,"d")

1 0

Question

Question

Date comparison calculation when one date is in a table?

Answer

Replies

Sign in to reply to this post.