Calculate number of hours of vacation being requested

asked on January 23 • Show version history

I have a Leave Request form, with date fields for full day vacation requests (Start Date and End Date) and separate datetime fields for partial days (DateTimeStart and DateTime End).

When a person requests full days, I'd like to calculate the number of vacation hours they are taking, based on the number of days between the Start Date and End Date, assuming there are 8 vacation hours for each calendar day that is not a weekend or holiday.

When a person requests partial days, the hours requested calculation will be based on DateTime fields, so hopefully that calculation will be easier.

The form is using the Modern Layout designer.

Thank you for any suggestions and assistance.

0 0

SELECTED ANSWER

replied on January 23

In formulas, look into NETWORKDAYS to Calculate this. Holidays would likely be an array. Unfortunately LF provides no documentation for the Holiday piece. Usually use Exceljet as a fallback to see what they say about it
=NETWORKDAYS(startDate, endDate, [holidays], [weekDays])

0 0

View 1 previous reply

replied on January 23

The Holidays parameter for NETWORKDAYS requires a list of dates. I'm not sure if I can just put the dates in a SQL Server table and query them into a single line field on the form and pass that as a parameter...but I'll try it and see if it works.

0 0

replied on January 23

Steve,

I found another post that seems to indicate that I can pass the holidays list from a table. I've tried doing that with a Forms table populated from SQL Server, but I'm getting an error. Got any suggestions?

Here's the formula:

=NETWORKDAYS(Start_Date, End_Date, Holidays_2.Holiday_Date)

And here's the error (which showed up as soon as I selected the Start Date:

0 0

replied on January 26

It turns out that the Holiday Date field needs to be a date field, and not a single line text box. The formula now works.

0 0

replied on January 26

Oh, also...I tried using the AI search in the online documentation and it was suggesting using the JOIN function to bring in a delimited list of dates from the table for the holidays parameter. That was wrong; I just needed to pass the parameters as actual date fields for Start Date, End Date, and HolidayDates.

1 0

Question

Question

Calculate number of hours of vacation being requested

Answer

Replies

Sign in to reply to this post.