IF Formula between two dates

Hi there,

I'm trying to write an IF(AND formula that will calculate the correct number of work hours based on start and end dates and % of commitment.

Row 1 = Column month being calculated (07/01/23 = July 23)

Row 2 = Number of workable hours in the month

Currently using the formula below -- it wasn't coming up with any errors at first, but now that it's a column that falls within the start/end date I'm getting an invalid operation error. If I IFERROR it, it doesn't calculate accurately.

=IF(AND([Jun-23]1 >= [Start Date]@row, [Jun-23]1 <= [End Date]@row), [Jun-23]2 * Commitment@row, 0)

TIA :)

Find more posts tagged with

Accepted answers

All comments

Debbie Sawyer

@Kelly W

I believe this to be due to your [Jul-23] column NOT being a Date data type. You are asking it to compare a Text value date (cell [Jul-23]1) to a date value ([Start Date]). This will cause an invalid data type error.

Try setting up your Row1 values in Summary Fields in the Sheet summary pane instead, then you can set them to Date data types without the rest of the [Jul-23] column needing to be a date.

Kind regards

Debbie

Kelly W

Thanks Debbie, I'll give that a try.

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.