Date Range Validation

Jason Voorhies

@Paul McGuinness Appreciate you!

Here is a screenshot of the helper column values:

Jason Voorhies

@Paul McGuinness I thought it might have something to do with the NETWORKDAYS function and the start/end date being less than the April 2023 helper column values. Then I tested with against another row with an older date (2/14/2023) and it returned an expected positive number.

Paul McGuinness

Hi @Jason Voorhies

Think its sorted, tested using the same data you had in your snip.

As they say its me not you, lol 😂

Basically I had factored in where the end date predated the specific month but not where the start date was after the specific month so it was still showing a negative calculation instead of zeroing out and moving on.

Took that into account and seemed to do the trick, updated formula below (added bit in bold).

=IF(OR($[Start Date]@row > [ND May 23]$3,$[End Date]@row < [ND May 23]$2), 0, NETWORKDAYS(MAX($[Start Date]@row, [ND May 23]$2), MIN($[End Date]@row, [ND May 23]$3)) * $[Hourly Rate]@row * $[Hrs/week]@row / 5)

Have a look and let me know if that's resolved it for you.

Thanks

Paul

Jason Voorhies

@Paul McGuinness I couldn't see the forest through the trees on that one. Thank you so very much! Applied it down the column with everything calculating correctly.

If I'm ever in the UK (assuming that is where you're located) the next pint is on me!

Have a great and restful weekend!

Jason

Paul McGuinness

@Jason Voorhies

Me neither! glad its sorted, yep I'm in the UK and always happy to receive a free drink!

Have a good weekend.

Thanks

Paul