Formula for returning value based on dates

I have a main sheet and a reference sheet. On the main sheet there is a Payroll Period which is based on a Shift Date column. However, I want to add a formula that refers to a reference sheet whereby I have two dates. If the Shift date falls within the Start Date and End Date of a pay period, I want the formula to return the value from the period Column see below.

Main sheet:

Reference sheet:

Ive tried using:

=INDEX(COLLECT({Period}, {Pay Period Start}, [Shift Date]@row >=@cell, {Pay Period End}, [Shift Date]@row <= @cell), 1) and am getting an #Invalid value error and cannot figure out what is wrong.

Any thoughts?

Find more posts tagged with

Accepted answers

Paul Newcome

Make sure all three columns are date type columns. What are the formulas used to populate the dates in the reference sheet?

All comments

Paul Newcome

Make sure all three columns are date type columns. What are the formulas used to populate the dates in the reference sheet?

Jaz693

that could be the issue, Thanks Paul! those formulas are point to date columns, so I can see now how those columns could then be represented as non date columns. I'll give it a try.

Jaz693

that fixed it.

Paul Newcome

Happy to help. 👍️

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.