Dynamic Date Validation and Computation

Jason Voorhies

Hello!

I'm almost embarrassed to post the following because I recognize it as a horribly constructed formula, but I am experimenting. First, let me highlight my use case:

I have a sheet (first screen shot) that pulls in Start/End Date, Hrs/week, and Hourly Rate via INDEX formula. I'm attempting to have the sheet dynamically calculate a dollar value if the Start/End Date fall within a particular month. If it does not make the cell $0. I'm aware based on previous feedback that I need feeder cells or a reference sheet. I've created the reference sheet (second screen shot), but the formula is not surprisingly spitting out a 'UNPARESABLE' error message.

I think this is a fairly complex ask, but if anyone can provide some guidance I'd really appreciate it.

Jason Voorhies

I should also add this is how I have it currently working, but as I indicated, it isn't dynamic so it simple calculates the month even if it falls outside of the Start/End date range.

Paul Newcome

It looks like you have some IDNEX functions missing and some parenthesis misplaced. Are you able to paste the formula here instead of just a screenshot?

Jason Voorhies

@Paul Newcome

Here it is...thanks!

=IF([End Date]@row < {Date Reference Sheet Last Day of Month}, MATCH([End Date]@row, $1, 0, NETWORKDAYS(MAX($({Date Reference Sheet First Day of Month}, MATCH([Start Date]@row, $1, MIN(${Date Reference Sheet Last Day of Month}, {Date Reference Sheet First Day of Month}, 0))) *$[Hourly Rate]@row * [Hrs/week]@row / 5)

Paul Newcome

Now that I look at it closer, I'm not sure it is just a couple of syntax issues. Try this instead:

=IF(AND(YEAR([Start Date]@row)<= 2023, MONTH([Start Date]@row)<= 4, YEAR([End Date]@row)>= 2023, MONTH([End Date]@row)>= 4), rate_calculation_portion, 0)

Jason Voorhies

@Paul Newcome

I think this is on the right track as it leaves the cell as $0.00 when the month isn't in the range (Start Date 7/1/2022 and End Date 3/3/2023), but it won't calculate when the range does. For example, Start Date is 7/1/2022 and End Date of 6/30/24. So, April 2023 should calculate, right?

=IF(AND(YEAR([Start Date]@row) <= 2023, MONTH([Start Date]@row) <= 4, YEAR([End Date]@row) >= 2023, MONTH([End Date]@row) >= 4), *$[Hourly Rate]@row * [Hrs/week]@row / 5, 0)

Paul Newcome

It should be. Are you able to provide a screenshot of it in the sheet?

Jason Voorhies

@Paul Newcome

Two part screen shot with the row highlight showing formula:

Same cell with no value in for April 2023:

Paul Newcome

Your second year function should be equal to or greater than 2023, not 2024.

Jason Voorhies

@Paul Newcome Adjusted but still zero. It seems to think the range is False, hence the $0.00?

Paul Newcome

Can you provide another screenshot similar to the last?

Jason Voorhies

Here is the adjusted formula:

=IF(AND(YEAR([Start Date]@row) <= 2023, MONTH([Start Date]@row) <= 4, YEAR([End Date]@row) >= 2023, MONTH([End Date]@row) >= 4), *$[Hourly Rate]@row * [Hrs/week]@row / 5, 0)

Paul Newcome

What happens if you remove all formatting from the hourly rate column? Is it left or right justified?