What formula can I use to return a value if the value is in between 2 dates?

Hi!

I have two sheets. Sheet #1 is a support schedule with columns for every day of the schedule, each column is a dropdown menu column. I extracted the date in text form for each day as shown in the first screenshot. For each day we have different resources assigned in a different column.

In sheet #2, I want to have my resources listed by week and pulled the data automatically. The formula I'm envisioning is something like IF(Day Date coming from sheet #1) is between (Start Date sheet#2) and (End Date sheet#2) then return the name of resource.

My main issues are:

1- The column type where I want to return the value is a contact type.

2- The Day date coming from sheet #1 is not from a date type column and it's an entire row, not a column, so it's text. And the dates I'm trying to compare with are in a date column.

I was thinking that maybe it'll be easier to assign each date a week number since I also have those criteria in my sheet#2 and work with those instead of the between dates. Would this be possible? any ideas on what the formula could look like?

Any help or inspiration would be much appreciated! TIA!

Find more posts tagged with

Formulas

Accepted answers

All comments

Julie Fortney

Hi @montserrat, do you have screenshots you could provide with dummy data? It might help me better visualize what you'd like to accomplish.

One initial question - if you have different resources assigned each day of a week on Sheet 1, the values you want returned on Sheet 2 would be multiple contacts, correct?

montserrat

That's correct @Julie!

I actually had a pro-desk session for this specific question and the conclusion was that I cannot work with "dates" in a string/text form and expect them to behave as a date value. So, everything that I was trying to accomplish was not possible in the end with the format we were working with. We're redoing it so we can have a cleaner base. Thank you for answering though!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.