# Dynamic Date Validation and Computation

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.

• 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.

• 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?

• 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)

• 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)

• 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)

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

• Two part screen shot with the row highlight showing formula:

Same cell with no value in for April 2023:

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

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

• Can you provide another screenshot similar to the last?

• 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)

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!