You are viewing limited content. For full access, please sign in.

Question

Question

Half Day calculation

asked on February 24, 2020

Hello,

As per the : https://answers.laserfiche.com/questions/102127/Calculation-in-Form-for-leave-application#102212

 

I want a little help, I have 2 Radio buttons & would want the end result to be reflected as per their selection...

halfday

halfday1

can you please tell me how to get it in this case?

0 0

Answer

SELECTED ANSWER
replied on March 6, 2020

Here is a way to use built-in Forms calculations to achieve this, using a table. The result will behave like this:

The bare-bones calculation which should go into the “Total Days” field in the “Time Table” is shown below with an explanation. To get a more fully featured calculation, keep reading!
=SUM(IF(INDEX(TimeTable.Half_Day,ROW())="Yes",VALUE("-0.5"),VALUE("0")),IF(INDEX(TimeTable.Half_Day_1,ROW())="Yes",VALUE("-0.5"),VALUE("0")),NETWORKDAYS(INDEX(TimeTable.Start,ROW()), INDEX(TimeTable.End,ROW())))

 

Breaking it apart:

INDEX(TimeTable.Half_Day,ROW())

This piece will grab the value of the Half_Day radio button within this row of the table.

IF(INDEX(TimeTable.Half_Day,ROW())="Yes",VALUE("-0.5"),VALUE("0"))

This piece will output the number -0.5 if the Half_Day radio button is checked and the number 0 otherwise. This function repeats itself for the Half_Day_1 radio button, since it’s the same functionality.

NETWORKDAYS(INDEX(TimeTable.Start,ROW()), INDEX(TimeTable.End,ROW())))

The net work days function will calculate the number of business days between two dates. We are again using the INDEX() function to make sure that we do the math with the two dates on this row.

SUM(…)

Sum this all together – the number of days, adding negative 0.5 days for each half day taken.

 

You can add on to this calculation to make it more robust as shown below:

  • Use the MIN() and MAX() functions to get the minimum/maximum of dates. This way you don’t get calculation errors when one date is blank, and your users can’t get negative days by putting a later date into Start than into End.
  • Show 0 days when no dates are entered but the Half Day radio buttons are checked. Again, preventing anyone from requesting negative days.

 

=IF(SUM(IF(INDEX(TimeTable.Half_Day,ROW())="Yes",VALUE("-0.5"),VALUE("0")),IF(INDEX(TimeTable.Half_Day_1,ROW())="Yes",VALUE("-0.5"),VALUE("0")),NETWORKDAYS(MIN(INDEX(TimeTable.End,ROW()),INDEX(TimeTable.Start,ROW())), MAX(INDEX(TimeTable.End,ROW()),INDEX(TimeTable.Start,ROW()))))<0, VALUE("0"), SUM(IF(INDEX(TimeTable.Half_Day,ROW())="Yes",VALUE("-0.5"),VALUE("0")),IF(INDEX(TimeTable.Half_Day_1,ROW())="Yes",VALUE("-0.5"),VALUE("0")),NETWORKDAYS(MIN(INDEX(TimeTable.End,ROW()),INDEX(TimeTable.Start,ROW())), MAX(INDEX(TimeTable.End,ROW()),INDEX(TimeTable.Start,ROW())))))

I know it’s long – it’s just an IF statement that says “evaluate this long statement, and if it comes out negative, output 0, else output the value of this [same] long statement”.

With this approach, you can also allow users to add multiple rows to the table, and use Forms calculations like =SUM(TimeTable.Total_days) to get the total days requested, if you turn your Total Days column into a number type and set it to allow 1 decimal place. Then you can further use calculations to compare the number of days available from your lookup to the days requested. You should change the “total days” and “days available” fields to read-only, as well.

0 0

Replies

replied on February 26, 2020

Anyone?

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.