Question

Date Time Calculation

Forms Forms Layout Designer

Updated May 22, 2023

asked on May 19, 2023

Hi, I'm on Forms 11 version: Professional Version 11.0.2212.30987. I am using the New Designer on this project. I'm needing to find out do I get the total amount in the Total Hours field? I do know I have to go to Advanced tab but i just don't know what fx I need to use to accomplish. Can someone guide me in the right direction?

Formss 11 Date_Time Calculation.png (8.12 KB)

| Download

0 0

Replies

replied on May 19, 2023

If you haven't already, you should bookmark this page:

https://doc.laserfiche.com/laserfiche.documentation/11/administration/en-us/Subsystems/Forms/Content/FieldCalculations.htm

Our team refers to this often when creating formulas on form fields.

I believe you can probably get what you need using DATEDIF.

I want to also note that we told our end users we couldn't do this on our time reporting forms because we can't account for when staff take their breaks/lunches, etc. Just something to think about (can -vs- should).

2 0

replied on May 19, 2023

You probably also want to look at the INDEX and ROW formulas since it looks like you might be working in a table or collection.

If you are trying to retrieve the value from another field on the same row you can use INDEX and ROW to retrieve it.

Retrieves the value from another field in the same table/collection row:
=INDEX(table_variable.field_variable, ROW())

Retrieves and adds together the values from two other field in the same table/collection row:
=SUM(INDEX(table_variable.field_variable1, ROW()),INDEX(table_variable.field_variable2, ROW()))

0 0

replied on May 19, 2023

Here's an example of one I did recently in our environment.

On my table, I needed them to be specific 15 minute increments, so I didn't use a time field, I used a drop-down populated with the possbile options. The hours field does the calculation for each row.

The formula has to check for blank values to return a 0 instead of an error, so the whole thing is nested within two layers of IF statements.

Because this is just looking at the time without factoring in date, it uses the TIMEVALUE formula, and a simple subtraction of end_time minus start_time.

Here's the calculation for hours on each row:

=IF(INDEX(date_and_times_table.end_time,ROW())="",0,IF(INDEX(date_and_times_table.start_time,ROW())="",0,(24*(TIMEVALUE(INDEX(date_and_times_table.end_time,ROW()))-TIMEVALUE(INDEX(date_and_times_table.start_time,ROW()))))))

And here's the calculation for the total at the bottom:

=SUM(date_and_times_table.total_hours_formula)

I've set the Hours field and the Total Hours field to have a valid value of 0 to 99 with 2 decimal places. This means it automatically throws an error if the value is negative (if the end time is before the start time).

1 0

replied on May 20, 2023

Thanks everybody for your knowledge!!!! I really do appreciate it.

1 0

replied on May 19, 2023

Something tells me that I'm going to have to separate these combine fields into two fields.

Formss 11 Date_Time Calculation2.png (73.28 KB)

| Download

0 0

replied on May 19, 2023 • Show version history

Not necessarily. It really depends more on what your use case is.

You can get the numerical date value from the combined field via the DATEVALUE formula and the time value from that same field via the TIMEVALUE formula.

Here's an example of a start date and time and and end date and time (these are not in a table, and this is not including formulas to handle the fields being blank). This calculates the number of hours between the start date and time and the end date and time:

=24*((DATEVALUE(end_date_time)+TIMEVALUE(end_date_time))-(DATEVALUE(start_date_time)+TIMEVALUE(start_date_time)))

0 0

replied on May 20, 2023

Thanks Matthew, I tried it and it works. And you're right as long as it's not in a collection. However, I'm needing this to work in a collection because we need the option to Add more date request if needed. Funny thing, is that there are only two on the hard copy. But once the directors saw the option to Add as needed they prefer it with that add option. So if all else fails, leave the Total Hours field open for hard coding.

0 0

replied on May 22, 2023

To include it in a table or collection, we need to wrap the variable references within INDEX formulas as mentioned in a separate comment. Instead of end_date_time and start_date_time, we need INDEX(collection_variable.end_date_time,ROW()) and INDEX(collection_variable.start_date_time,ROW()).

So it'll look something like this:

=24*((DATEVALUE(INDEX(collection_variable.end_date_time,ROW()))+TIMEVALUE(collection_variable.end_date_time,ROW())))-(DATEVALUE(collection_variable.start_date_time,ROW()))+TIMEVALUE(collection_variable.start_date_time,ROW()))))

Note that I didn't actually test that formula in a form, I just modified the one from the earlier post, which I did test in a form.

0 0

You are not allowed to follow up in this post.

Question

Question

Date Time Calculation

Replies

Sign in to reply to this post.