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.

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.